Export (0) Print
Expand All

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

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.

  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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft