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 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:

• Understanding SQL Server Indexes and difference between Clustered and Nonclustered Indexes
• Rebuild Index vs Reorganize Index Operations
• Understanding Fill Factor and its value
• Relation between Page Split and Fill Factor

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

• Author
• Recent Posts

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.

Follow me:

Latest posts by Manvendra Deo Singh (see all)

• How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
• How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
• How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023