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.

Operation

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.

rollno	firstname	lastname	stream
11	Amy	Williams	Arts
13	Laura	Wells	Science
23	Rizwan	Ahmed	Commerce
25	Nick	Jones	Science
29	Michael	Bull	Science
30	Val	Kostner	Science
33	Wayne	John	Commerce
35	Julie	Summers	Arts

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;

index_name	index_description	index_keys
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