Guidelines for Enabling Indexes and Constraints
After an index is disabled, it remains in a disabled state until it is rebuilt or dropped. You can enable a disabled index by rebuilding it by using one of the following methods:
ALTER INDEX statement with the REBUILD clause
CREATE INDEX with the DROP_EXISTING clause
DBCC DBREINDEX
By using one of these statements, the index is rebuilt and the index status is set to enabled.
Note
Rebuilding a disabled clustered index cannot be performed when the ONLINE option set to ON.
After rebuilding the index, any constraints that were disabled because of disabling the index must be manually enabled. PRIMARY KEY and UNIQUE constraints are enabled by rebuilding the associated index. This index must be rebuilt (enabled) before you can enable FOREIGN KEY constraints that reference the PRIMARY KEY or UNIQUE constraint. FOREIGN KEY constraints are enabled by using the ALTER TABLE CHECK CONSTRAINT statement.
How a Disabled Clustered Index Affects Nonclustered Indexes
When a disabled clustered index is rebuilt or dropped, the effect to nonclustered indexes depends on the state, whether disabled or enabled, of both index types. The following table summarizes the effects.
When the clustered index is disabled or enabled and the nonclustered index is disabled, the clustered index action has the following results on the disabled nonclustered index.
When the clustered index action is |
The disabled nonclustered index |
|---|---|
ALTER INDEX REBUILD. |
Remains disabled. |
ALTER INDEX ALL REBUILD. |
Is rebuilt and enabled. |
DROP INDEX. |
Remains disabled. |
CREATE INDEX WITH DROP_EXISTING. |
Remains disabled. |
Creating a new clustered index, behaves the same as ALTER INDEX ALL REBUILD.
Allowed actions on nonclustered indexes associated with a clustered index depend on the state, whether disabled or enabled, of both index types. The following table summarizes the allowed actions on nonclustered indexes.
When the nonclustered index action is |
And the clustered and nonclustered indexes are disabled. |
Or the clustered index is enabled and the nonclustered index is either disabled or enabled. |
|---|---|---|
ALTER INDEX REBUILD. |
The action fails. |
The action succeeds. |
DROP INDEX. |
The action succeeds. |
The action succeeds. |
CREATE INDEX WITH DROP_EXISTING. |
The action fails. |
The action succeeds. |