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

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.

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.

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

Follow me on TwitterFollow me on FaceBook