Disable and Enable SQL Server Indexes

This article explains how to disable an Index in SQL Server. It is a helpful feature to figure out whether an index is helpful without dropping the index. Once we disable an index, the query optimizer does not create the query execution plans.

Why do we disable an index?

You might need to disable an index in the following cases:

• During the performance tuning activities

• Bulk-inserts

• Figure out whether the index is valid or not

Check Index and their status in a SQL Server table

Firstly, let’s check the existing indexes on a table and their status for the table [SalesLT].[SalesOrderDetail].

SELECT   name AS [Index Name]
             , type_desc AS [Index Type]
             , index_id AS [Index ID]
             , CASE IS_DISABLED
                           WHEN 0 THEN 'Enabled'
                           ELSE 'Disabled'
                    END AS [Index Usage]
       FROM SYS.INDEXES
WHERE OBJECT_ID = OBJECT_ID('SalesLT.SalesOrderDetail')
GO

The following figure shows the [SalesLT].[SalesOrderDetail] table has a clustered and two non-clustered indexes.

Disable a SQL Server Non-Clustered index

We can disable a non-clustered index using the ALTER TABLE statement. The t-SQL script requires two inputs.

· Index name: Specify the Index name that we want to disable

· Table name: Specify the table name in which index exists

The following script disables the index [IX_SalesOrderDetail_ProductID] on the [SalesLT].[SalesOrderDetail] table.

ALTER INDEX IX_SalesOrderDetail_ProductID ON SalesLT.SalesOrderDetail DISABLE
GO

As shown below, the specfic index [IX_SalesOrderDetail_ProductID] is disabled.

Enable a disabled Index in SQL Server

To enable a disabled index, we need to rebuild it. The following query rebuilds the index [IX_SalesOrderDetail_ProductID] and enables query optimizer usage.

ALTER INDEX IX_SalesOrderDetail_ProductID ON SalesLT.SalesOrderDetail REBUILD;   
GO  

Disable a Clustered index in SQL Server

If we disable a clustered index, SQL Server disables the clustered index and all non-clustered indexes on the specific table.

For example, the following query disables the clustered index [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID], and we can see the script shows non-clustered indexes are disabled as well.

ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON SalesLT.SalesOrderDetail DISABLE;   
GO  

Enable a disabled clustered index in SQL Server

If we enable (REBUILD) a disabled clustered index, it does not enable the non-clustered indexes. As shown below, the clustered index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID is enabled while the remaining non-clustered indexes are disabled.

ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON SalesLT.SalesOrderDetail REBUILD;   
GO

You need to run individual statements for rebuilding the non-clustered index to enable it.

ALTER INDEX AK_SalesOrderDetail_rowguid ON SalesLT.SalesOrderDetail REBUILD;   
GO  
ALTER INDEX IX_SalesOrderDetail_ProductID ON SalesLT.SalesOrderDetail REBUILD;   
GO  

Disable all SQL Server indexes on a table

Suppose you perform a bulk insert on a table and disable all indexes on a table. For this purpose, we can use ALTER INDEX ALL...DISABLE statement, and it disables clustered and non-clustered index on the table.

For example, the below script disables all indexes on the [SalesLT].[SalesOrderDetail] table.

ALTER INDEX ALL ON SalesLT.SalesOrderDetail DISABLE;  
Go

Enable all SQL Server indexes on a table

Similarly, we can use ALTER INDEX ALL..REBUILD statement to enable all indexes without using the individual statements.

ALTER INDEX ALL ON SalesLT.SalesOrderDetail  REBUILD; 
Go