How to move data between File Groups in 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.

CREATE TABLE TestTable
(
	ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	SomeData1 INT NOT NULL,
	SomeData2 CHAR(5000)
)
GO

-- Create a supporting Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_SomeData1 ON TestTable(SomeData1)
GO

-- Insert around 800 MB of data
DECLARE @i INT = 0
WHILE (@i < 100000)
BEGIN
	INSERT INTO TestTable (SomeData1, SomeData2)
	VALUES (@i, REPLICATE('a', 5000))

	SET @i += 1
END
GO

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:

-- Add a new file group to the database
ALTER DATABASE TestDatabase ADD FILEGROUP CustomFileGroup
GO

-- Add a new file to the previous created file group
ALTER DATABASE TestDatabase ADD FILE
(
	NAME = 'CustomFile1', 
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustomFile1.ndf',
	SIZE = 1048576KB,
	FILEGROWTH = 65536KB
) TO FILEGROUP CustomFileGroup
GO

-- Add a new file to the previous created file group
ALTER DATABASE TestDatabase ADD FILE
(
	NAME = 'CustomFile2', 
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustomFile2.ndf',
	SIZE = 1048576KB,
	FILEGROWTH = 65536KB
) TO FILEGROUP CustomFileGroup
GO

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

-- Move the Clustered Index into the newly created file group
CREATE UNIQUE CLUSTERED INDEX PK__TestTabl__3214EC279CAEC6A7 ON TestTable(ID)
WITH
(
	DROP_EXISTING = ON
)
ON CustomFileGroup
GO

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:

-- Create a supporting Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_SomeData1 ON TestTable(SomeData1)
WITH
(
	DROP_EXISTING = ON
)
ON CustomFileGroup
GO

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

-- Shrink the MDF file in the PRIMARY file group
DBCC SHRINKFILE ('TestDatabase' , 0)
GO

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.

-- Create a new Clustered Index on the Heap table that moves the data into the custom file group
CREATE UNIQUE CLUSTERED INDEX idx_ci ON TestTable(ID)
ON CustomFileGroup
GO

-- Drop the previous created Clustered Index again ;-)
DROP INDEX idx_ci ON TestTable
GO

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

Summary

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

6 thoughts on “How to move data between File Groups in SQL Server”

  1. 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),
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
    (
    [Row_ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG_DynamicTables],
    ) 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.

    1. Klaus Aschenbrenner

      Hello Klaus,

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

      -Klaus

  2. 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.

    1. Klaus Aschenbrenner

      No, because if you have multiple files in a file group, the data is spread across all the files.

Leave a Reply to Klaus Cancel Reply

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

Enjoy one of my newest Online Trainings:

Design, Deploy, and Optimize SQL Server on VMware

EUR 699 incl. 20% VAT