I know how to use Enterprise Manager to move a table to a different filegroup. But I want to automate the process by using T-SQL to move a table—not just its indexes—to a different filegroup. How do I do this?
Enterprise Manager moves a table from one filegroup to another without using an undocumented task, but T-SQL doesn't have a command that does the same thing. The easiest way to move a table to another filegroup is to create a clustered index on the table. If the table already has a clustered index, you can use the CREATE INDEX command's WITH DROP_EXISTING clause to recreate the clustered index and move it to a particular filegroup. When a table has a clustered index, the leaf level of the index and the data pages of the table essentially become one and the same. The table must exist where the clustered index exists, so if you create or recreate a clustered index—placing the index on a particular filegroup—you're moving the table to the new filegroup as well.
Alternatively, you can do what Enterprise Manager does: create a temporary table to hold the data from your table, then create a brand-new table on the desired filegroup. Then, Enterprise Manager issues all the appropriate T-SQL commands to ensure that permissions, indexes, triggers, and so on are properly recreated on the new object. In my opinion, manually moving the index is much simpler and more straightforward because it reduces the risk of missing a step during the recreation process.
