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