SQL Server Disable Indexes

Summary: in this tutorial, you will learn how to use the ALTER TABLE statement to disable the indexes of a table.

SQL Server Disable Index statements

To disable an index, you use the ALTER INDEX statement as follows:

ALTER INDEX index_name
ON table_name
DISABLE;
Code language: SQL (Structured Query Language) (sql)

To disable all indexes of a table, you use the following form of the ALTER INDEX statement:

ALTER INDEX ALL ON table_name
DISABLE;
Code language: SQL (Structured Query Language) (sql)

If you disable an index, the query optimizer will not consider that disabled index for creating query execution plans.

When you disable an index on a table, SQL Server keeps the index definition in the metadata and the index statistics in nonclustered indexes. However, if you disable a nonclustered or clustered index on a view, SQL Server will physically delete all the index data.

If you disable a clustered index of a table, you cannot access the table data using data manipulation language such as SELECT, INSERT, UPDATE, and DELETE until you rebuild or drop the index.

SQL Server disable index examples

Let’s take some examples of disabling indexes to have a better understanding.

A) Disabling an index example

This example uses the ALTER INDEX to disable the ix_cust_city index on the sales.customers table:

ALTER INDEX ix_cust_city 
ON sales.customers 
DISABLE;
Code language: SQL (Structured Query Language) (sql)

As a result, the following query, which finds customers who locate in San Jose , cannot leverage the disabled index:

SELECT    
    first_name, 
    last_name, 
    city
FROM    
    sales.customers
WHERE 
    city = 'San Jose';
Code language: SQL (Structured Query Language) (sql)

Here is the estimated query execution plan:

SQL Server Disable Index - disable one index example

B) Disabling all indexes of a table example

This statement disables all indexes of the sales.customers table:

ALTER INDEX ALL ON sales.customers
DISABLE;
Code language: SQL (Structured Query Language) (sql)

Hence, you cannot access data in the table anymore.

SELECT * FROM sales.customers;
Code language: SQL (Structured Query Language) (sql)

Here is the error message:

The query processor is unable to produce a plan because the index 'PK__customer__CD65CB855363011F' on table or view 'customers' is disabled.Code language: JavaScript (javascript)

Note that you will learn how to enable the index in the next tutorial.

In this tutorial, you have learned how to use the ALTER INDEX statement to disable indexes of a table.

Was this tutorial helpful?