Moving a Table to Another Filegroup by Rebuilding Its Clustered Index

You can move a table to another filegroup without an export and reload by rebuilding its clustered index there. One statement does the move. But it moves only part of the table, and the part it leaves behind is the one people forget.

The disk is full and the new drive is empty

A familiar situation. The drive that holds your data file is nearly full, and the storage team gives you a fresh one. You add a filegroup on the new drive. Now you need a big table to live there, without a weekend of exporting and importing.

The trick is that a clustered index is the table. Rebuild it on the new filegroup and the rows go with it. The demo creates a database called SqlAuthorityDemo with a second filegroup named FastDisk, and drops the database at the end.

The data file path comes from the instance default, so create nothing by hand. The folder must exist and the service account must be able to write to it.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
CREATE DATABASE SqlAuthorityDemo;
GO
ALTER DATABASE SqlAuthorityDemo ADD FILEGROUP FastDisk;

DECLARE @Path nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @Sql nvarchar(max) = N'ALTER DATABASE SqlAuthorityDemo
    ADD FILE (NAME = N''SqlAuthorityDemo_fast'',
              FILENAME = N''' + @Path + N'SqlAuthorityDemo_fast.ndf'',
              SIZE = 8MB, FILEGROWTH = 8MB)
    TO FILEGROUP FastDisk;';
EXEC (@Sql);

Create a table and see where its parts live

The table has a clustered index, a nonclustered index and a varchar(max) column. One row holds 20000 characters in that column, which is too big to sit on the page. SQL Server keeps it in separate large-object storage.

A small view lists each piece of storage and the filegroup it sits in, so we can reuse it. Right now everything is in PRIMARY, including the LOB_DATA of the clustered index.

USE SqlAuthorityDemo;
GO
CREATE VIEW dbo.StorageLayout AS
SELECT OBJECT_NAME(i.object_id) AS TableName, i.index_id AS IndexId, i.name AS IndexName,
       au.type_desc AS Storage, ds.name AS Filegroup
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units AS au
  ON (au.type IN (1, 3) AND au.container_id = p.hobt_id)
  OR (au.type = 2 AND au.container_id = p.partition_id)
JOIN sys.data_spaces AS ds ON ds.data_space_id = au.data_space_id
WHERE au.total_pages > 0 AND OBJECTPROPERTY(i.object_id, 'IsMSShipped') = 0;
GO
CREATE TABLE dbo.MoveDemo (Id int NOT NULL, Note varchar(100) NOT NULL, Notes varchar(max) NULL);
CREATE UNIQUE CLUSTERED INDEX CX_MoveDemo ON dbo.MoveDemo (Id);
CREATE INDEX IX_MoveDemo_Note ON dbo.MoveDemo (Note);

INSERT dbo.MoveDemo VALUES
    (1, 'retained', REPLICATE(CONVERT(varchar(max), 'x'), 20000)),
    (2, 'also retained', 'short');

SELECT IndexName, Storage, Filegroup
FROM dbo.StorageLayout
WHERE TableName = 'MoveDemo'
ORDER BY IndexId, Storage;

Rebuild the clustered index on the new filegroup

Now the move. Recreate the same index, with the same name and keys, and add DROP_EXISTING and the new filegroup. Keep the key and uniqueness exactly as before. Otherwise you change more than the location.

CREATE UNIQUE CLUSTERED INDEX CX_MoveDemo ON dbo.MoveDemo (Id)
WITH (DROP_EXISTING = ON)
ON FastDisk;

SELECT IndexName, Storage, Filegroup
FROM dbo.StorageLayout
WHERE TableName = 'MoveDemo'
ORDER BY IndexId, Storage;

SELECT Id, Note FROM dbo.MoveDemo ORDER BY Id;

Read the first result slowly. The in-row data of the clustered index is now in FastDisk. But the LOB_DATA of the same index is still in PRIMARY. The nonclustered index also stays in PRIMARY. Both rows survive with their values, so nothing was lost.

This is the part that bites. You free no space on the old drive if the big text lives there. Check the layout after the move, not before.

Moving the large-object data takes a copy

The LOB location is chosen when the table is created. To change it, create a new table with TEXTIMAGE_ON, copy the rows, and swap names in a quiet window. The block builds the new table and shows its layout. Both the in-row data and the LOB data now sit in FastDisk.

CREATE TABLE dbo.MoveDemoNew (Id int NOT NULL, Note varchar(100) NOT NULL, Notes varchar(max) NULL)
ON FastDisk TEXTIMAGE_ON FastDisk;
CREATE UNIQUE CLUSTERED INDEX CX_MoveDemoNew ON dbo.MoveDemoNew (Id);

INSERT dbo.MoveDemoNew SELECT Id, Note, Notes FROM dbo.MoveDemo;

SELECT IndexName, Storage, Filegroup
FROM dbo.StorageLayout
WHERE TableName = 'MoveDemoNew'
ORDER BY IndexId, Storage;

SELECT COUNT(*) AS RowsCopied FROM dbo.MoveDemoNew;

Check the whole move, then clean up

On a real table, also check row counts, constraints, nonclustered indexes and the next backup. Plan room for the rebuild, because it needs working space and writes to the log. The last block removes the demo database.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Next time you move a table, look at where every piece ended up.

A clustered index rebuild is not a full table move, it is the first half of one.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.