How to: Move an Existing Index to a Different Filegroup (SQL Server Management Studio)

  • Article

This topic describes how to move an existing index from its current filegroup to a different filegroup by using SQL Server Management Studio. If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.

To move an existing index to a different filegroup or partition scheme

  1. In Object Explorer, connect to an instance of the SQL Server 2005 Database Engine and then expand that instance.

  2. Expand Databases, expand the database that contains the table with the specific index, and then expand Tables.

  3. Expand the table in which the index belongs and then expand Indexes.

  4. Right-click the index to be moved and then select Properties.

  5. On the Index Properties dialog box, select the Storage page.

  6. Select the filegroup in which to move the index.

    You cannot move indexes that were created as a result of a unique or primary key constraint by using the Index Properties dialog box. To move these indexes, drop the constraint using ALTER TABLE with the DROP CONSTRAINT option and then recreate the constraint on the desired filegroup using ALTER TABLE with the ADD CONSTRAINT option.

    If the table or index is partitioned, select the partition scheme in which to move the index. For more information about partitioned indexes, see Partitioned Tables and Indexes.

    If you are moving a clustered index, you can use online processing. Online processing allows concurrent user access to the underlying data and to nonclustered indexes during the index operation. For more information, see Performing Index Operations Online.

    On multiprocessor computers using SQL Server 2005 Enterprise Edition, you can configure the number of processors used to execute the index statement by specifying a maximum degree of parallelism value. For more information, see Configuring Parallel Index Operations.

  7. Click OK.

See Also

Concepts

Placing Indexes on Filegroups
Understanding Files and Filegroups

Other Resources

Database Engine How-to Topics
Indexes How-to Topics
Understanding Indexes
SQL Server Management Studio Tutorial

Help and Information

Getting SQL Server 2005 Assistance