How to Move a Table to a Different File Group

Monday Apr 3rd 2017 by Greg Larsen
Share:

There may be a time when you want to move a table from one file group to another. It's easy if it contains a clustered index. Read on to learn more.

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
USE master;
GO
CREATE DATABASE MoveTable 
ON PRIMARY (NAME = MoveTable1, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MoveTable1.mdf'),
FILEGROUP MoveFile2
(NAME = MoveFile2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MoveTable2.ndf') ;
GO

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
USE MoveTable;
GO
CREATE TABLE dbo.ToMove
(ID int,
SampleData varchar(10)) ON [PRIMARY]
CREATE CLUSTERED INDEX IX_ID ON MoveTable.dbo.ToMove(ID);
GO 

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]
GO

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved