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.

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.

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