Disable Enable Indexes SQL Server

One of the most common questions on forums is how to disable enable indexes SQL Server. Index can be disabled or enabled as shown below

USE AdventureWorks2014
-- Query1: disable index
ALTER INDEX [IX_Address_StateProvinceID]
ON Person.Address DISABLE
-- Query2: Check index status
SELECT Name,is_disabled from sys.indexes 
where object_id=object_id('Person.Address')

The query 1 above disables the index [IX_Address_StateProvinceID] on Person.Address table. The query 2 checks the status of the index. The output of query 2 is shown below.

1_Disable or Enable Indexes in SQL Server

The index [IX_Address_StateProvinceID] is disabled. To enable an index it needs to be rebuilt as shown below.

ALTER INDEX [IX_Address_StateProvinceID]
ON Person.Address REBUILD

Re run the query 2 above to check the index status.

When writing demos for this blog post I ran into an interesting thing. I disabled a clustered index and was unable to select data from the table or perform any other operation.

CREATE table tblone (Sno int identity, col1 int)
CREATE CLUSTERED INDEX ix_sno on tblone(sno)
-- disable the clustered index 
ON dbo.tblone DISABLE
select * from dbo.tblone

The above query terminates with below error.

Msg 8655, Level 16, State 1, Line 12

The query processor is unable to produce a plan because the index ‘ix_sno’ on table or view ‘tblone’ is disabled.

Furthermore, disabling primary key clustered index will disable the all foreign keys referencing that primary key. An example of same is shown below

2_Disable or Enable Indexes in SQL Server

As shown in above snapshot, the SQL server displays warning when disabling primary key clustered that the foreign key referencing tblone is disabled.


Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook


Leave a Reply

Your email address will not be published.