O'Reilly logo

Microsoft® SQL Server 2012 Unleashed by Alex T. Silverstein, Chris Gallelli, Paul T. Bertucci, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Disabling Indexes

Another feature available in SQL Server 2012 is the capability to set an index as disabled. When an index is disabled, the definition of the index is maintained in the system catalogs, but the index itself contains no index key rows. Disabling an index prevents use of the index by any queries against the table. Disabling a clustered index also prevents access to the underlying table data.

You can manually disable an index at any time by using the ALTER INDEX DISABLE statement:

ALTER INDEX Product_number_index ON Production.Product DISABLE

The reasons you might want to disable an index include the following:

Correcting a disk I/O or allocation error on an index page and then rebuilding the index ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required