Moving to a New Filegroup

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

Problem

Roger is the chief database architect for several SQL Server 2000 and7.0 systems. To improve performance and accelerate backup and recoveryon the machines, he wants to place several large tables and indexes intheir own filegroups. These tables and indexes include tables withclustered UNIQUE constraints, tables with clustered indexes, and a fewlarge nonclustered indexes. What steps must Roger take to move thetables and indexes from the PRIMARY filegroup to a newly added filegroupnamed FG_CRIT without dropping or recreating the tables?

Solution

Roger can use the ON clause of the ALTER TABLE and CREATEINDEX statements to move the identified tables and indexes to the newfilegroup. By moving the clustered indexes or the clustered UNIQUEconstraints 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 theUNIQUE constraints, he can use the ALTER TABLE statement that follows todrop and recreate the constraints in the new filegroup. However, ifother tables reference the UNIQUE constraint, he also needs to drop andrecreate the related foreign key constraints.

BEGIN TRANSACTIONALTER TABLE tblname DROP CONSTRAINT uq_tblname_idALTER 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 theDROP_EXISTING clause and the ON clause, as the following exampleshows:

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

By following these steps, Roger can successfully move the requiredtables and indexes to a new filegroup and enjoy improved performancewith minimal impact.