How to move data between File Groups in SQL Server

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

I don’t know how often I have been asked this question in my life: “How can I move data between File Groups in SQL Server?”. You know the problem: you have a default configuration of a database with only a PRIMARY file group, and at some point in time you learn that a custom file group with multiple files within it would be a better idea. But how can you now move the existing data from the PRIMARY file group into the newly added file group?

The goal of this blog posting is to show you how you can move data between file groups. First I will cover Clustered- and Non-Clustered Indexes, and later on I will show you how to move data with Heap Tables. Let’s start!

Moving Clustered- and Non-Clustered Indexes

Normally you should always have a Clustered Index defined on your table. With an existing Clustered Index in place it is quite easy to move the table data (which is the Clustered Index) into a different file group. In the following listing I create a simple Clustered- and Non-Clustered Index for a table, and insert around 800 MB of test data into that table.

When you execute the system stored procedure sp_help on your table, you can see that both indexes (the Clustered Index and the Non-Clustered Index) reside on the PRIMARY file group.

Both indexes are stored in the PRIMARY file group...

And now imagine that I have finally convinced you that a custom file group with multiple files is a good idea, and you follow this best practice:

The problem is now that all your existing data still resides on the PRIMARY file group. But how can you move it to the newly added file group? The answer to this question is quite simple: you just rebuild all your existing indexes (Clustered Indexes, Non-Clustered Indexes) and you specify the newly added file group as the target! Let’s start with the Clustered Index (the name of it was extracted from sys.indexes):

When you execute sp_help again, you can already see that SQL Server has moved the Clustered Index completely into the different file group.

The Clustered Index has been moved now into the custom file group.

And now we continue with the Non-Clustered Index:

And finally we can shrink down the data file of the PRIMARY file group to get rid of the old allocated space:

When you now insert another 800 MB of data, you can finally verify that all the new allocations have happened in the newly added file group, and that the PRIMARY file group remains small. Mission accomplished!

Moving Heap Tables

If you want to move the data from Heap Tables into a custom added file group, it gets a little bit trickier. The main problem is that SQL Server doesn’t provide you with a direct way to move the data from a Heap Table between file groups.

Therefore we have to perform a workaround: you have to temporarily create on your Heap Table a Clustered Index (that moves the data into the custom file group), and afterwards you drop the Clustered Index to get the Heap Table back.

I know this is a little bit ugly, but there are no other efficient solutions. Another option would be also to create a new Heap Table in the custom file group, move the data into that table, drop the old Heap Table, and rename the new one. Still not a perfect solution…


Moving data between file groups can be easy or hard – depending on whether there is a Clustered Index in place or not. If you have a Clustered Index, you only have to rebuild all your indexes into the custom file group. If you have to deal with Heap Tables, you have to temporarily add a Clustered Index (which moves the table data to the other file group), and drop it afterwards. Not really a perfect solution…

Thanks for your time,



  • Klaus,

    there is (at least) one case that is not covered: If you specified a text file group for nvarchar(max).

    CREATE TABLE [dbo].[TestTable](
    [Row_ID] [int] IDENTITY(1,1) NOT NULL,
    Test_Column NVARCHAR(MAX),
    [Row_ID] ASC
    ) ON [FG_DynamicTables]; –The latter ON is the culprit

    If you drop and re-create the (clustered) primary key constraint, the text data (nvarchar(max) columns) will remain in the old file group. Hence, one has to do the create-second-table-in-new-file-group-and-copy-data job as well.

    • Klaus Aschenbrenner

      Hello Klaus,

      Thanks for your comment.
      True, BLOB data types make your live a little more complicated 😉


  • Thomas Franz

    You forgot an important piece: TEXTIMAGE_ON

    If your table contains any VARCHAR(MAX) / VARBINARY(MAX) / TEXT / IMAGE column (maybee XML too) this data will be stored on extra pages (at least if they so large that the whole record would be > 8000 byte) in the filegroup you specified as TEXTIMAGE_ON at creation time of the table (default = PRIMARY).

    Sadly there is – as far I know – no way to change this (B)LOB-filegroup beside creating a new table, moving all data to it, dropping the old one and renaming the new table to the orginial file name. This could become a very heavy task, if you have many indexes, foreign keys, constraints, MS_Description’s, privileges, SCHEMA_BINDING functions / views … on the table.

It`s your turn

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


Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top