Moving Heaps to another Filegroup

For large databases on a server with access to multiple physical volumes (more spindles) it is a good practice to move large objects out of the PRIMARY filegroup.



For performance, this can assist by giving you access to different and/or dedicated physical media.

For backups and restores, you may find it useful to perform backups on filegroups or files.

And it is usually simple enough to move tables from filegroup to filegroup, except when that table is without a clustered index (a heap).

Our sample setup:

--Create our sample heap

CREATE TABLE LARGELOGTABLE (id int identity(1,1) not null, text1 varchar(1000) null) ON [PRIMARY] 
--Add a Primary Key Clustered Index on the PRIMARY file group

ALTER TABLE LARGELOGTABLE ADD CONSTRAINT PK_LARGELOGTABLE PRIMARY KEY CLUSTERED (ID) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]

Add rows to taste.

insert into dbo.LARGELOGTABLE (text1)

select replicate(char(round(rand()*100,0)),round(rand()*100,0))

go

declare @x integer

set @x = 1

while @x < 18

begin

       insert into dbo.LARGELOGTABLE (text1)

       select replicate(char(round(rand()*100,0)),round(rand()*100,0))

       from LARGELOGTABLE

set @x = @x + 1

end

go

select count(1) from dbo.LARGELOGTABLE

-----------

131072

Simple enough.  I've used this same script to dump random data into a table for multiple labs.

Here's how to move a table with a clustered index from the default [PRIMARY] filegroup to a new filegroup called [SECONDFILEGROUP].  The key is the DROP_EXISTING.

--Move the Clustered Index to SECONDFILEGROUP

CREATE UNIQUE CLUSTERED INDEX PK_LARGELOGTABLE ON LARGELOGTABLE (ID) WITH (DATA_COMPRESSION = PAGE, DROP_EXISTING = ON) ON [SECONDFILEGROUP]

But what if we don't have a clustered index?  Simple enough, just remove the bit about DROP_EXISTING.

--Move the Clustered Index to SECONDFILEGROUP

CREATE CLUSTERED INDEX PK_LARGELOGTABLE ON LARGELOGTABLE (ID) WITH (DATA_COMPRESSION = PAGE) ON [SECONDFILEGROUP]

DROP INDEX PK_LARGELOGTABLE ON dbo.LARGELOGTABLE

Notice that I didn't specify UNIQUE in that CREATE CLUSTERED INDEX command.  It created a non-unique clustered index.  If your table doesn't have a clustered index or a unique column?  No problem, same strategy.

Also, don't forget that DROP INDEX line... though you really should be asking yourself if adding a CLUSTERED INDEX to that table is a good idea (it is).


A non-unique clustered index is better than no clustered index.  Would I ever intentionally design a non-unique clustered index?  Not in an OLTP system.  Maybe a data warehouse, but I've made design decisions to avoid that.  I always prefer something to be unique in a table.  But I digress.