The objective of this SQL Server tutorial is to teach you how to disable an index associated with a table or view.
An index is a disabled in one of the following scenarios.
- 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.
- 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.
- 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.
- 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] DISABLE;
The basic syntax of the command to delete all indexes on a table or view is as follows.
ALTER INDEX ALL ON [table_name|view_name] DISABLE;
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.
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.
The same can also be checked using the SP_HELPINDEX system stored procedure.
EXEC SP_HELPINDEX students;
|ix_stream_index||nonclustered located on PRIMARY||stream|
|PK_students||clustered, unique, primary key located on PRIMARY||rollno|
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.
ALTER INDEX PK_students ON students DISABLE; 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.
ALTER INDEX ALL ON students DISABLE; 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