Congratulations to Sal Terillo, DBA for New York City-based Intrasphere Technologies, and Albert Decker, senior DBA/Web developer at agentLIFE in Tampa, Florida. Sal won first prize of $100 for the best solution to the April Reader Challenge, "Moving to a New Filegroup." Albert won second prize of $50. Here's a recap of the April Reader Challenge and the solution.

Problem


Roger is the chief database architect for several SQL Server 2000 and 7.0 systems. To improve performance and accelerate backup and recovery on the machines, he wants to place several large tables and indexes in their own filegroups. These tables and indexes include tables with clustered UNIQUE constraints, tables with clustered indexes, and a few large nonclustered indexes. What steps must Roger take to move the tables and indexes from the PRIMARY filegroup to a newly added filegroup named FG_CRIT without dropping or recreating the tables?

Solution


Roger can use the ON clause of the ALTER TABLE and CREATE INDEX statements to move the identified tables and indexes to the new filegroup. By moving the clustered indexes or the clustered UNIQUE constraints to the new filegroup, he can also move the table data, because the leaf level of these indexes contains the data.

Although Roger can't directly drop and recreate the indexes for the UNIQUE constraints, he can use the ALTER TABLE statement that follows to drop and recreate the constraints in the new filegroup. However, if other tables reference the UNIQUE constraint, he also needs to drop and recreate the related foreign key constraints.

BEGIN TRANSACTION
ALTER TABLE tblname DROP CONSTRAINT uq_tblname_id
ALTER TABLE tblname ADD CONSTRAINT uq_tblname_id UNIQUE CLUSTERED( id
)
ON
\[FG_CRIT\]
COMMIT TRANSACTION

To move the indexes, Roger can use the CREATE INDEX statement with the DROP_EXISTING clause and the ON clause, as the following example shows:

CREATE CLUSTERED INDEX ix_tblname_name ON tblname( name )
WITH DROP_EXISTING
ON \[FG_CRIT\]

By following these steps, Roger can successfully move the required tables and indexes to a new filegroup and enjoy improved performance with minimal impact.