Microsoft® SQL Server 2008 R2 Unleashed

Tip

Another reason for disabling a nonclustered index is to reduce the space requirements when rebuilding the index. If an index to be rebuilt is not disabled, SQL Server requires enough temporary disk space in the database to store both the old and new versions of the index. However, if the index is disabled first, SQL Server can reuse the space required for the disabled index to rebuild it. No additional disk space is necessary except for temporary space required for sorting, which is only about 20% of the index size.

The following example disables a nonclustered index on the Production.Product table:

ALTER INDEX [AK_Product_Name] ON [Production].[Product] DISABLE

One point to keep in mind when an index is disabled is that it is not readily ...