Programming in MS SQL Server 2012

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

GO

 

-- Enable Nonclustered Index on Employee Table --

 

ALTER INDEX IX_Employee_Nonclustered ON dbo.Employee

REBUILD

GO


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

GO

 

-- Enable Nonclustered Index on Employee Table --

 

CREATE NONCLUSTERED INDEX IX_Employee_Nonclustered

          ON dbo.Employee(Dept_ID)

     WITH (DROP_EXISTING = ON)

GO


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

GO

 

-- Enable Nonclustered Index on Employee Table --

 

DBCC DBREINDEX ("dbo.Employee",IX_Employee_Nonclustered)

GO


Query Output Screen



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

-- Example 164 --

 

USE EBusiness

GO

 

-- Enable All Indexes on Employee Table --

 

ALTER INDEX ALL ON dbo.Employee

REBUILD

GO


Query Output Screen



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

-- Example 165 --

 

USE EBusiness

GO

 

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

GO


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.

* * * * *


Email Your Comment To AUTHOR