Enabling and Disabling an Index in SQL Server 2005

In SQL Server 2005, Microsoft introduced the “ALTER INDEX” command. This alter command is used to enable or disable the indexes, re-build the indexes or re-organize the indexes. One can also set specific options like ‘allow_row_locks’, ‘allow_page_locks’ etc. at the index level.  In this blog post, we will talk about disabling and enabling the index. Let us first create a table along with some indexes and populate it with some data.

CREATE TABLE TEST(TEST_ID INT,TEST_DESC VARCHAR(20),TEST_DATE DATETIME)
GO
CREATE UNIQUE CLUSTERED INDEX TEST_IND_1 ON TEST(TEST_ID)
GO
CREATE INDEX TEST_IND_2 ON TEST(TEST_DESC)
GO
CREATE INDEX TEST_IND_3 ON TEST(TEST_DATE)
GO

INSERT INTO TEST VALUES(1,’Oracle’)
INSERT INTO TEST VALUES(2,’SQLServer’)
INSERT INTO TEST VALUES(3,’DB2 LUW’)

Disabling non-clustered index:

Whenever a non-clustered index is disabled, only the access to index is disabled. Optimizer will ignore the disabled index even though your query has the perfect filter criteria in which an index can be used. Also, whenever a non-clustered index or index on a view is disabled, index data physically gets deleted.  Execute the following query and look at the execution plan.

SET SHOWPLAN_TEXT ON
SELECT TEST_ID,TEST_DESC FROM TEST WHERE TEST_DESC = ‘ORACLE’
GO

|–Index Seek(OBJECT: ([DECIPHER].[dbo].[TEST].[TEST_IND_2]), SEEK: ([DECIPHER].[dbo].[TEST].[TEST_DESC]=[@1]) ORDERED FORWARD)

Plan text indicates that an index seek operation is performed on the table.  Also selecting row count for an index will show that there are 3 rows for a given index.

SELECT rowcnt FROM sys.sysindexes WHERE name = ‘TEST_IND_2’
GO

Now let us disable the index and run the same queries again to check the plan and to check the row count. Following is the syntax to disable the index.

ALTER INDEX TEST_IND_2 ON TEST DISABLE
GO

Re-executing above two queries prior to disable command will have following results.
|–Clustered Index Scan(OBJECT: ([DECIPHER].[dbo].[TEST].[TEST_IND_1]), WHERE: ([DECIPHER].[dbo].[TEST].[TEST_DESC]=[@1]))

Instead of performing an index seek operation on TEST_IND_2,  the optimizer performs a clustered index scan operation beause we disabled the index. Also checking the rowcount will result into 0,  since the data from the index pages got deleted when we disabled the index.

Disabling clustered index:

Whenever we disable a clustered index, it disables all the non-clustered indexes of the table as well. Once clustered index is disabled, user can not access the the underlying table data but index definition remains in the system catalog. Another important point to note is, even though user cannot access the data, data still remains in the B-Tree until index is dropped or rebuilt but it is not maintained. Let us disable the clustered index now.

ALTER INDEX TEST_IND_1 ON TEST DISABLE
GO

Warning: Index ‘TEST_IND_2’ on table ‘TEST’ was disabled as a result of disabling the clustered index on the table.
Warning: Index ‘TEST_IND_3’ on table ‘TEST’ was disabled as a result of disabling the clustered index on the table.

Now let us try to select the data.

select * from test

Issuing select statement will result into following error. Same message will be displayed if one trys to perform any DML operation.

Msg 8655, Level 16, State 1, Line 1
The query processor is unable to produce a plan because the index ‘TEST_IND_1’ on table or view ‘test’ is disabled.

Enabling index:

Indexes can be enabled using ‘ALTER INDEX REBUILD’ command or ‘CREATE INDEX WITH DROP_EXISTING’ command. Index can be rebuilt online also but we will cover it separately. We can enable individual index on the table or we can enable all the indexes on the table using ‘ALL’ clause. Again one thing to keep in mind is that, enabling clustered index does not enable all non-clustered indexes. You have to enable them individually. Following is the syntax to enable specific index.

ALTER INDEX TEST_IND_1 ON TEST REBUILD
GO

or to enable all indexes on the table

ALTER INDEX ALL ON TEST REBUILD
GO

We can see whether index is disable or not by accessing sys.indexes view. Following is the query to check whether index is disable or not.

SELECT name, is_disabled FROM sys.indexes WHERE name like ‘TEST%’