SQL Server Disable Index

Learning Objective

The objective of this SQL Server tutorial is to teach you how to disable an index associated with a table or view.

Why Disable?

An index is a disabled in one of the following scenarios.

  1. To check the performance of an index – An index is oftentimes disabled to check query performance without the index so as to figure out whether the index is having a beneficial effect or whether we can do without the index.
  2. To perform bulk updates – An index is also disabled when performing bulk updates (i.e. bulk inserts, deletes etc. from a file) on a table for faster update. Otherwise SQL server has to update the index while updating the table to ensure that the changes made to the table is replicated in all the indexes at the right place and position. This slows down the update process considerably. So, the common practice is to disable the indexes before performing the update and then rebuilding it.

Impact of Disabling

Disabling an index has the following impacts depending on the type of the index.

  1. If a non-clustered index is disabled it becomes unavailable for the query generator to generate estimated execution plan and for the query processor to query the table with the index. This helps us determine the performance of queries without using the index. The metadata of the index however remains intact and the index can be enabled as and when required.
  2. If a clustered index is disabled the underlying table becomes unavailable for user querying. That is DML commands do not work on the table until the index is either enabled or dropped. The metadata of the index however remains intact and the index can be enabled as and when required. Also, all other indexes on the table and on views of the table become disabled on disabling the clustered index. So, disabling a clustered index is like disabling all indexes on a table and its views.


An index is disabled using the ALTER INDEX command. It is possible to delete one index at a time or all the indexes associated with a table or view at one go in a single command.

SQL Server Disable Index Syntax

The basic syntax of the command to delete an index is as follows.

ALTER INDEX index_name
ON [table_name|view_name]

The basic syntax of the command to delete all indexes on a table or view is as follows.

ON [table_name|view_name]

SQL Server Disable Indexes Examples

Let us consider a table called students in the database. The table contains basic student information including student name, roll number and stream of study. The table is represented below. The rollno column is the primary key column.

11Amy                                              Williams                                         Arts               
13Laura                                            Wells                                            Science            
23Rizwan                                           Ahmed                                            Commerce           
25Nick                                             Jones                                            Science            
29Michael                                          Bull                                             Science            
30Val                                              Kostner                                          Science            
33Wayne                                            John                                             Commerce           
35Julie                                            Summers                                          Arts               
Table: students

The table has indexes associated with it. We can see the indexes in the screenshot taken from SQL Server Management Studio (SSMS) below. It has a default clustered index on the primary key rollno and an additional non-clustered index on the stream column.

SQL Server Disable Index

The same can also be checked using the SP_HELPINDEX system stored procedure.

ix_stream_indexnonclustered located on PRIMARYstream
PK_studentsclustered, unique, primary key located on PRIMARYrollno

It also has a view called students_view which has 2 indexes defined on it as can be seen in the figure below. One of them is a clustered index and another a non-clustered index.

With the help of the above sample scenario we will see the index disable operation in action and its ramifications.

1) Disabling a single Index

In this first example we will disable the clustered index associated with the students table with the below command. We can see from the command output that the command does not only disable the index on the table but also the indexes in the view students_view for the table.

ON students

Warning: Clustered index 'ix_students_view' on view 'students_view' referencing table 'students' was disabled as a result of disabling the index 'PK_students'.
Warning: Index 'ix_nc_students_view' on view 'students_view' was disabled as a result of disabling the clustered index on the view.

After disabling the index, we will run a SELECT query on the table to select all rows from the table. From the below we can see that the query fails since the query processor is produce to plan to execute the query.

SELECT * FROM students;

Msg 8655, Level 16, State 1, Line 1
The query processor is unable to produce a plan because the index 'PK_students' on table or view 'students' is disabled.
Completion time: 2020-09-28T21:30:31.5493724+05:30

2) Disabling All Indexes on a Table

In this example we will disable both the clustered and non-clustered indexes on the students table (i.e. all associated indexes) with a single ALTER query as below. From the query output you can see that it has succeeded and also that disabling the clustered index will automatically disable all indexes on a table. If we run any query now it will fail since all indexes including the clustered index has been disabled.

ON students

Warning: Index 'ix_stream_index' on table 'students' was disabled as a result of disabling the clustered index on the table.
Completion time: 2020-09-28T21:41:07.9068388+05:30