Moving table(s) to a different filegroup in SQL 2005

In SQL Server 2005, a new clause ‘MOVE TO’ was added to the ALTER TABLE command to allow for moving a table to a different filegroup. MOVE TO clause is used along with DROP CONSTRAINT clause in the ALTER TABLE syntax. When a primary key constraint or a constraint which created a clustered index is dropped, leaf level data rows of the clustered index are placed in non clustered table. In SQL Server 2005, when clustered index is dropped (By dropping constraint that created clustered index), one can move table to new filegroup or partition scheme in same transaction by using this new ‘MOVE TO’ option. Let us see this with the help of an example. For our test purpose, we will create test database, couple of filegroups and a table and will populate it with some data.

USE master
GO
CREATE DATABASE TEST
GO

ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_1
GO
ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_2
GO

ALTER DATABASE TEST
ADD FILE
( NAME = TEST1,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_1.ndf’,
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP TEST_DATA_1
GO

ALTER DATABASE TEST
ADD FILE
( NAME = TEST2,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_2.ndf’,
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP Test_DATA_2
GO

USE TEST
GO

CREATE TABLE TAB1
(
TAB1_ID INT IDENTITY(1,1),
TAB1_NAME VARCHAR(100),
CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
) ON TEST_DATA_1 –- Filegroup we created.
GO

INSERT INTO TAB1(TAB1_NAME)
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
GO

Execution of sp_help system stored procedure for table TAB1 will indicate that filegroup for table is TEST_DATA_1.

sp_help TAB1

Selected output of above command is

Data_located_on_filegroup
—————————
TEST_DATA_1

index_name index_description
———- ——————————————————-
PK_TAB1 clustered, unique, primary key located on TEST_DATA_1

constraint_type constraint_name
————————– —————-
PRIMARY KEY (clustered) PK_TAB1

Above result indicates that PK_TAB1 constraint is used to create the clustered index on the table TAB1. Please note that whenever a primary key constraint is defined, and clustered index does not exist on the table, SQL Server will create clustered index for the primary key constraint. It is not the same for UNIQUE constraint. Unique constraint/ index will always be non-clustered unless other wise it is specified during creation of constraint or index. We had covered this before in the differences between a primary key and a unique constraint blog post.

In order to move the table to a different file group, we need to use the drop constraint command along with the MOVE TO option as shown below. Once the table is moved to a new file group, we can re-create the primary key constraint.

ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO TEST_DATA_2)
GO
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
GO

After executing the above command, table TAB1 will now reside on filegroup TEST_DATA_2. If you re-execute sp_help stored procedure, it will show the filegroup change for table TAB1.

sp_help TAB1

Data_located_on_filegroup
—————————
TEST_DATA_2

We need to keep in mind the following restrictions when the ‘MOVE TO’ option is used:

• MOVE TO is not valid for indexed views or non-clustered indexes.
• The partition scheme or filegroup must already exist.
• If MOVE TO is not specified, the table will be located in the same partition scheme or filegroup as was defined for the clustered index.

This becomes very handy when we want to move some large tables to their own filegroup for performance reasons or for backing up individual tables via filegroup backup. We can also keep this into consideration when we need to clone table definition and data.