Pam Brisjar (10/16/2008)
This is a hack, plain and simple. If you really want to move the table, the best way is to create a "temp: table on the new filegroup. move the data, drop the existing table, rename the "temp" table and then re-create all of the FK's, constraints, and indexes.It's a bit of a PITA but it's the only way to ensure the whole thing gets moved properly. Besides, that's pretty much what SQL Server is doing behind the scenes anyhow.
By best practices the nc indexes should (probably) be on their own filegroup anyhow but that's a different issue.
Can you please give justification for why you call this a 'hack' and why you feel your mechanism is the 'best way'??
Per 2005 BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/bff3a4bf-4bac-40be-a9bf-e75b1c978711.htm), microsoft recommends this:
Move a table from one filegroup to another by dropping the clustered index and specifying a new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX statement or by using the CREATE INDEX statement with the DROP_EXISTING clause.
That isn't exactly what the author specified, but same intent/results.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service