MS SQL Server 2012 - DBA Articles

Enable Index

After an index is disabled, it remains in a disabled state until it is rebuilt or dropped particular index.
The following example describes to enable a disabled IX_Employee_Nonclustered index on Employee table by using ALTER INDEX statement.

-- Example 161 --

USE EBusiness

-- Enable Nonclustered Index on Employee Table --

ALTER INDEX IX_Employee_Nonclustered ON dbo.Employee

REBUILD

Query Output Screen

The following example describes to enable a disabled IX_Employee_Nonclustered index on Employee table using CREATE INDEX statement with DROP_EXISTING option.

-- Example 162 --

USE EBusiness

-- Enable Nonclustered Index on Employee Table --

CREATE NONCLUSTERED INDEX IX_Employee_Nonclustered

ON dbo.Employee(Dept_ID)

WITH (DROP_EXISTING = ON)

Query Output Screen

The following example describes to enable a disabled IX_Employee_Nonclustered index on Employee table using DBCC DBREINDEX option.

-- Example 163 --

USE EBusiness

-- Enable Nonclustered Index on Employee Table --

DBCC DBREINDEX ("dbo.Employee",IX_Employee_Nonclustered)

Query Output Screen

The following example describes to enable ALL indexes on Employee table.

-- Example 164 --

USE EBusiness

-- Enable All Indexes on Employee Table --

ALTER INDEX ALL ON dbo.Employee

REBUILD

Query Output Screen

The following example describes to enable ALL indexes on Employee table by using DBCC DBREINDEX option.

-- Example 165 --

USE EBusiness

DBCC DBREINDEX ("dbo.Employee", " ")

Query Output Screen

Limitations and Restrictions
01. 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.
02. Rebuilding a disabled clustered index cannot be performed when the ONLINE option is set to ON.
03. 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.

Clustered Index Action	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.

04. 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.

Nonclustered Index Action	When both the clustered and nonclustered indexes are disable.	When the clustered index is enabled and the nonclustered index is in either state.
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.

* * * * *

Your Comment To AUTHOR