There may be a time when you want to move a table from one file group to another. There could be lots of different reasons for wanting to move your data file. One of the reasons why you might want to do this is to improve performance. It is easy to move a table from one filegroup to another if it contains a clustered index.
To show you how to move a table from one file group to another let me create an example.
First let me create a sample database using the following code:
-- Create Sample database
CREATE DATABASE MoveTable
ON PRIMARY (NAME = MoveTable1, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MoveTable1.mdf'),
(NAME = MoveFile2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MoveTable2.ndf') ;
With this code I have created a database named “MoveTable” that contains two filegroups: PRIMARY and MoveFile2.
Now let me create a sample table that resides on the PRIMARY file group using the following code:
-- create table ToMove on Primary filegroup
CREATE TABLE dbo.ToMove
SampleData varchar(10)) ON [PRIMARY]
CREATE CLUSTERED INDEX IX_ID ON MoveTable.dbo.ToMove(ID);
Here I have created a table named “ToMove” on the PRIMARY filegroup and then created a clustered index on the table named “IX_ID”. To verify this table is on the PRIMARY filegroup I will run the following code:
-- Determine which filegroup table ToMove resides on
SELECT o.[name] AS TableName, i.[name] AS IndexName, f.[name] as FileGroup
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.[name] = 'ToMove'
When I run this code I get following output:
TableName IndexName FileGroup
------------------------- -------------------------- ----------------------------
ToMove IX_ID PRIMARY
As you can see by the output above my sample table “ToMove” resides on the PRIMARY filegroup and has a clustered index name of IX_ID.
To move this table to the second file group name “MoveFile2” all I have to do is run the following command:
-- Move table to filegroup MoveTable2
CREATE CLUSTERED INDEX IX_ID ON MoveTable.dbo.ToMove(ID)
WITH(DROP_EXISTING=ON,Online=ON) ON [MoveFile2]
This command moves the table from the PRIMARY filegroup to the MoveFile2 filegroup by dropping and recreating the clustered index. It does this operation while the index is online. You can verify that the table ToMove got moved by running the code above that displays the filegroup in which this table resides.
See all articles by Greg Larsen