Moving Tables / Indexes across filegroups

How to move a table / index to a new filegroup
This can be a costly operation in terms of time and I/O

1) Create the filegroup

ALTER DATABASE myDatabase ADD FILEGROUP NewFileGroup
GO

ALTER DATABASE myDatabase
ADD FILE
( NAME = N'NewFileGroupData'
, FILENAME = 'D:\Data\NewFileGroupData.mdf'
, SIZE = 5000MB
, FILEGROWTH = 10%)
TO FILEGROUP NewFileGroup
GO

2A) If the table has a primary key or unique constraint, drop and recreate the constraint ...

USE myDatabase  
GO  
   
ALTER TABLE my.table DROP CONSTRAINT PK_Move WITH (MAXDOP =1,MOVE TO NewFileGroup)
GO  
   
ALTER TABLE my.table ADD CONSTRAINT PK_Move PRIMARY KEY(id) WITH(MAXDOP =1)  
GO 

2B) For a clustered / non-clustered index (outside of a constraint), recreate the index
using the CREATE INDEX statement, with DROP_EXISTING = ON

USE myDatabase  
GO  
CREATE NONCLUSTERED INDEX [ix_movethisindex] ON [my].[table] 
(
 [column1] ASC,
 [column2] ASC,
 [column3] ASC
)WITH (DROP_EXISTING = ON, ONLINE = ON, DATA_COMPRESSION = PAGE) ON [NewFileGroup]
GO

Note : I use;

• ONLINE = ON for an ONLINE index build (Enterprise, Developer, and Evaluation editions only)
• DATA_COMPRESSION = PAGE (Am on SQL 2008 Enterprise and am utilising compression functionality)

2C) To move heaps (tables with no clustered indexes) across filegroups, create a clustered index on the new filegroup, and then remove it again. This has the downside of temporarily ordering the table.

USE myDatabase  
GO  

CREATE CLUSTERED INDEX [ix_temp] ON [my].[table] 
(
 ID ASC
)WITH ( ONLINE = OFF, DATA_COMPRESSION = PAGE) ON [NewFileGroup]
GO

DROP INDEX [ix_temp] ON [my].[table]