How To Disable and Enable Indexes in SQL Server

dbtut

6 years ago

Indexes can be disabled in SQL Server 2005 and later versions. If the index is disabled, it is necessary to rebuild the index to enable it again. If you disable Clustered Index, the table with the clustered index will be inaccessible.

Right click on the index you want to disable and click disable and you will see a screen like below. In this screen, it says; the index definition will be stored in metadata and if you want to re-enable the index, you should rebuild the index. You can disable it by clicking OK.

After you disable the Clustered Index, you will get an error as follows when you want to select the table.

Msg 8655, Level 16, State 1, Line 2

The query processor is unable to produce a plan because the index ‘IX_Clustered’ on table or view ‘FamousFood ‘ is disabled.

To re-enable a disable index, you must use the ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING commands.

Index Rebuild Operation

ALTER INDEX [IX_Clustered] ON [dbo].[FamousFood ] REBUILD

1	ALTER INDEX [IX_Clustered] ON [dbo].[FamousFood ] REBUILD

Drop Create Operation

CREATE UNIQUE CLUSTERED INDEX [IX_Clustered] ON [dbo].[FamousFood ]
(
	[ID] ASC
)WITH (DROP_EXISTING = ON)

CREATE UNIQUE CLUSTERED INDEX [IX_Clustered] ON [dbo].[FamousFood ]

(

[ID] ASC

)WITH (DROP_EXISTING = ON)

One or more indexes can be disabled automatically during upgrade. So it is necessary to pay attention to this during the upgrade.

Microsoft says that; “An index that uses system functions can bring a different result after the upgrade, or as a result of the collation change as part of the upgrade, the index can be sorted incorrectly, so this type of indexes will be disabled during the upgrade.”

If an index is disabled during the upgrade, the index name and the corresponding constraint name are displayed as a warning message. Thus, you can enable these indexes by rebuilding after upgrade. If you do not disable index and rebuild without “DROP_EXISTING=ON”, you need extra space for both the old and new index during the index rebuild process.

However, if you perform a rebuild process on another transaction after you disable the index, only about 20% temporary disk space is required for the sort operation. You do not need disk space except of this. You can disable unused indexes for a while before deleting them.

You may want to browse the article “How To Find Unused Indexes in SQL Server” to identify unused indexes.

You can use the following script to find disabled indexes.

SELECT o.name,i.name from sys.indexes i
    INNER JOIN sys.objects o on o.object_id = i.object_id
WHERE i.is_disabled = 1
ORDER BY o.name,i.name

SELECT o.name,i.name from sys.indexes i

INNER JOIN sys.objects o on o.object_id = i.object_id

WHERE i.is_disabled = 1

ORDER BY o.name,i.name