Sql-server – Moving a heap between filegroups

filegroupsheapsql-server-2008-r2

I would like to move the tables in my database from one filegroup (Primary) to a another (a new one). I can find lots of advice on this concerning tables with clustered indexes but all these tables in question are heaps. So far I implemented the solution found here:

http://gallery.technet.microsoft.com/scriptcenter/c1da9334-2885-468c-a374-775da60f256f#content

which essentially provides a script to create a clustered index for the table on a secondary filegroup (which essentially moves the data) then removes the index to return it to being a heap. This seems to work fine but may be too slow for our production requirements.

Are there any alternative solutions? This doesn't feel quite elegant enough for me to believe as the only method.

Note1: The tables must remain heaps. A 3rd party app enforces this.

Note2: The DB is about 150GB in size across 200 tables. 197 tables and 99.9% of the data will move to the secondary filegroup.

Best Answer

You're going to have to physically move the data - there is no getting around that. But I don't know who came up with the idea that doing so requires creating a clustered index - this just exchanges certain steps for others, and isn't going to require any different amounts of temporary space to accomplish than the method I outline below.

Why not create a second, empty heap on the new filegroup:

CREATE TABLE dbo.whatever_copy
(
  ... cols ...
)
ON [second filegroup];

Then script the indexes, constraints etc. from the original table, copy the data over, remove any constraints that need to be removed (e.g. foreign keys pointing to the original table), then drop the original table, and rename the new one. Finally, add the indexes and constraints to the new table.

 -- script out indexes, constraints etc.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

INSERT dbo.whatever_copy(cols) WITH (TABLOCK)
  SELECT cols FROM dbo.whatever;

 -- remove constraints etc.

DROP TABLE dbo.whatever;

EXEC sp_rename N'dbo.whatever_copy', N'whatever', N'OBJECT';

 -- add constraints etc.

COMMIT TRANSACTION;
Related Question