How to Move Table to Another Filegroup?

Suppose we had a database that has grown very large in size and located on the D drive that had a 100 GB Capacity. Now drive D is running out of space. We had other drives on the server where there was available disk space, so our immediate solutions will be to move large tables from D drive to different drive where there is enough space to make some room for your database.  To move table to another filegroup, we need to perform below steps.

  1. Identify the Large tables that we will be moving to another filegroup placed on drive E.
  2. Create new filegroup on disk E and if you have already a filegroup in this drive you can use that one.
  3. Add a data file to this newly created filegroup.
  4. Drop the clustered index from the table that has been identified for the movement and recreate it to the another filegroup.
  5. Create cluster index on the same table.

move table to another filegroup

Move a table to Another Filegroup

We can move table from one filegroup to another filegroup through dropping and recreating the clustered indexes. Below T-SQL codes that will move your complete table from filegroup1 to filegroup2. Run below ALTER TABLE statement to move a table named TAB1 that has a cluster index named PK_TAB1 to another filegroup TEST_DATA_2.

ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO TEST_DATA_2)
GO
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
GO

where TAB1 is table name PK_TAB1 is clustered index key and TEST_DATA_2 is target filegroup. It’s not necessary to create the another filegroup in to separate disk. This method will be useful if you want to move a table from one filegroup to another filegroup irrespective of the filegroup drives.

If your table doesn’t have a clustering index by any reason, you can still move table by creating a clustering index and then follow the same process to move it.

Related Articles:

I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *