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

Now create a new file group

ALTER DATABASE test
ADD FILEGROUP file_test

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

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

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

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