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
Комментарии