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.

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

  1. In Object Explorer, connect to an instance of the SQL Server 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 created using a unique or primary key constraint by using the Index Properties dialog box. To move these indexes, use the CREATE INDEX statement with the (DROP_EXISTING=ON) 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 Enterprise, 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