Move Table to Another, New or Different File Group

This week I’m working on reorganizing some of the tables in a database. I want to quickly move tables from one file group to another without having to recreate the whole table and copy the data over. Depending on each table’s situation this little trick is a handy helper.

I Want to Move a Simple Table

What is a simple table? In this example, it is one without any LOB (large object) data types such as varchar(max) or varbinary(max). Since the CLUSTERED INDEX defines where the data physically resides, I will recreate that INDEX using DROP_EXISTING and that will move the data. FAST and SIMPLE.

-- Move the table from PRIMARY file group to FGData file group.

CREATE UNIQUE CLUSTERED INDEX PK_MyTableDemo ON MyTableDemo([ID]) WITH DROP_EXISTING ON [FGData];

Need proof. I will view the properties.

I Want to Move a Table With LOB Data

No worries. Ican do that too. Let me recreate that demo table.

DROP TABLE  [dbo].[MyTableDemo];

CREATE TABLE [dbo].[MyTableDemo](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Column1] [varchar](50) NULL,
	[tsCaptured] [datetime] NULL,
	[Column2] [varchar](max) NULL,
 CONSTRAINT [PK_MyTableDemo] PRIMARY KEY CLUSTERED 
([ID] ASC ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

This time I will use the PARTITION FUNCTION and SCHEME to move the table in a similar fashion. I am aware that if I do this with a large table, it may blow up the size of the file group. So, I’m prepared with enough space, and code to shrink the file group afterward. And as always, I test in a development environment first.

CREATE PARTITION FUNCTION pf_MyTableDemo_temp ([int] ) AS RANGE RIGHT FOR VALUES (0);

CREATE PARTITION SCHEME ps_MyTableDemo_temp AS PARTITION pf_MyTableDemo_temp TO ([FGLobData],[FGLobData]);

CREATE UNIQUE CLUSTERED INDEX PK_MyTableDemo ON MyTableDemo([ID])  WITH (DROP_EXISTING = ON , ONLINE = ON) ON ps_MyTableDemo_temp([ID] );

CREATE UNIQUE CLUSTERED INDEX PK_MyTableDemo ON MyTableDemo([ID])  WITH (DROP_EXISTING = ON , ONLINE = ON) ON [FGLobData];

DROP PARTITION SCHEME ps_MyTableDemo_temp;

DROP PARTITION FUNCTION pf_MyTableDemo_temp;

CREATE UNIQUE CLUSTERED INDEX PK_MyTableDemo ON MyTableDemo([ID]) WITH DROP_EXISTING ON [FGData];

After the table move.

And I will remember to move my INDEXES as well.

DROP INDEX [dbo].[MyTableDemo].[IX_MyTableDemo_tsCaptured];	

CREATE NONCLUSTERED INDEX [IX_MyTableDemo_tsCaptured] ON [dbo].[MyTableDemo](tsCaptured ASC, Column1 ASC) ON FGIndex;

END OF TIP FOR TODAY.