Indexes

The index is not maintained while it is disabled.
The query optimizer does not consider the disabled index when creating query execution plans. Also, queries that reference the disabled index with a table hint fail.

You cannot create an index that uses the same name as an existing disabled index.

A disabled index can be dropped.

When disabling a unique index, the PRIMARY KEY or UNIQUE constraint and all FOREIGN KEY constraints that reference the indexed columns from other tables are also disabled. When disabling a clustered index, all incoming and outgoing FOREIGN KEY constraints on the underlying table are also disabled. The constraint names are listed in a warning message when the index is disabled. After rebuilding the index, all constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement.

Nonclustered indexes are automatically disabled when the associated clustered index is disabled. They cannot be enabled until either the clustered index on the table or view is enabled or the clustered index on the table is dropped. Nonclustered indexes must be explicitly enabled, unless the clustered index was enabled by using the ALTER INDEX ALL REBUILD statement.

The ALTER INDEX ALL REBUILD statement rebuilds and enables all disabled indexes on the table, except for disabled indexes on views. Indexes on views must be enabled in a separate ALTER INDEX ALL REBUILD statement.

Disabling a clustered index on a table also disables all clustered and nonclustered indexes on views that reference that table. These indexes must be rebuilt just as those on the referenced table.

The data rows of the disabled clustered index cannot be accessed except to drop or rebuild the clustered index.

You can rebuild a disabled nonclustered index online when the table does not have a disabled clustered index. However, you must always rebuild a disabled clustered index offline if you use either the ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING statement. For more information about online index operations.

The CREATE STATISTICS statement cannot be successfully executed on a table that has a disabled clustered index.

The AUTO_CREATE_STATISTICS database option creates new statistics on a column when the index is disabled and the following conditions exist:

• AUTO_CREATE_STATISTICS is set to ON
• There are no existing statistics for the column.
• Statistics are required during query optimization.

If a clustered index is disabled, DBCC CHECKDB cannot return information about the underlying table; instead, the statement reports that the clustered index is disabled. DBCC INDEXDEFRAG cannot be used to defragment a disabled index; the statement fails with an error message. You can use DBCC DBREINDEX to rebuild a disabled index.

Creating a new clustered index enables previously disabled nonclustered indexes. For more information, see Enable Indexes and Constraints.