March 22, 2011

SQL Server: Move Table to a new File Group




If a database is expected to grow rapidly, it becomes challenging to maintain the data files
in the same location/drive. To manage disk space well, it one solution to this problem is to identify large tables and accordingly move new data to a new File group, which can be mapped to a different drive path.

This article shows how to move a SQL Server Table from one File group to another. Let us see the steps. To demonstrate, we will create a sample table, find out the file group and then move it to a new File group:

Create a test table

CREATE TABLE test(i int , names varchar(100))

and see which File group is the table created in, using the following command:

EXEC sp_help test

See the fifth resultset. The index_description column says that the table is in the PRIMARY file group

filegroup3

Now create a new file group

ALTER DATABASE test
ADD FILEGROUP file_test

and add a logical path to this file, as shown below:

SQL move filegroup

alter database test
add file
(
name = test_readonly1,
filename = 'c:\file_test.ndf',
size = 10mb,
maxsize = 200mb,
filegrowth = 5mb
) to filegroup file_test;

Now to move table test to this File group, create an index on that table by specifying the
File Group

CREATE CLUSTERED INDEX IDX_i ON test(i)
ON file_test

and run the command again and observe the fifth resultset

EXEC sp_help test

SQL filegroup

As you can see, the index_description column says that the table has been moved to the file_test file group


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

3 Responses to "SQL Server: Move Table to a new File Group"
  1. Muhammad Azeem said...
    March 23, 2011 at 9:53 AM

    This is a nice article..
    Its very easy to understand ..
    And this article is using to learn something about it..

    c#, dot.net, php tutorial

    Thanks a lot..!

  2. B Shells said...
    March 27, 2011 at 8:22 PM

    Mr. Madhivanna this is a very nice article and easy to follow steps. I implemented the solution instantly without issues

  3. Madhivanan said...
    March 28, 2011 at 12:13 AM

    Thanks for the feebacks

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions