SQL Server: Move your Table from one File Group to another File Group

If you are finding most top 10 steps for SQL Server Performance tuning, you could get one of the items in the list is “multiple file-groups and various data-files increases the query performance”.This is correct.

You can move your fat table from master or primary data group to another custom file group.
It gives you more performance and very easy to do maintenance.

I am also asking this question to SQL DBA like “how to move a table from one File Group to another File Group?” and most of the times I am getting answers like “Create new file group -> Replicate table schema -> Move data -> Drop old table”.

The above answer is right, but instead of this great exercise, we can move a table to new file group by creating a clustered index only.

Here, You can access full demonstration.
Create a sample table:

Insert few dummies record:

Check the table Information:

SQL Server File Group Migration

Alter a database to add new file group:

Add new data file (.ndf):

Create a new CLUSTERED index on table within created new file group:

Check the table information:

SQLServer File Group Migration 2

If you need a space of old table, you can shrink your database:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

2 Comments on "SQL Server: Move your Table from one File Group to another File Group"

Notify of
Sort by:   newest | oldest | most voted

Hey Anvesh, nice write up but i am getting below error… please help me!

SQL Server – Error: 5042 – The file ‘FileName’ cannot be removed because it is not empty