What Happens If the Clustered Index is Disabled? – Interview Question of the Week #291

Question: What happens if the clustered index is disabled? The table’s rows remain, but SQL Server prevents access to them until the clustered index is rebuilt or dropped. Disabling it is not a way to keep the table usable while reducing index maintenance.

The easy detail to miss is that the clustered index’s leaf level holds the table data. Disable that access path and a SELECT cannot simply fall back to a different index. The database itself has not been taken OFFLINE; this particular table has become unavailable for data operations.

There is no ENABLE INDEX command

Rebuild the disabled index. For this isolated example, rebuilding ALL also restores the nonclustered index that disabling the clustered index disabled automatically. Run the whole script in one session:

-- This session owns this temporary table only.
IF OBJECT_ID('tempdb..#DisabledClusterDemo') IS NOT NULL
    THROW 50001, 'The demonstration table already exists.', 1;
CREATE TABLE #DisabledClusterDemo (ID int NOT NULL, Value varchar(20));
CREATE CLUSTERED INDEX CX_DisabledClusterDemo ON #DisabledClusterDemo(ID);
CREATE NONCLUSTERED INDEX IX_DisabledClusterDemo ON #DisabledClusterDemo(Value);
INSERT #DisabledClusterDemo VALUES (1,'Still here');
ALTER INDEX CX_DisabledClusterDemo ON #DisabledClusterDemo DISABLE;
-- Dynamic execution lets TRY/CATCH catch the disabled-index compilation error.
BEGIN TRY
    EXEC sys.sp_executesql N'SELECT ID, Value FROM #DisabledClusterDemo;';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
ALTER INDEX ALL ON #DisabledClusterDemo REBUILD;
SELECT ID, Value FROM #DisabledClusterDemo;
SELECT name, is_disabled FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#DisabledClusterDemo');

The first SELECT should report an error. After the rebuild, the row reads 1, Still here, and the two named indexes have is_disabled = 0. No application table is altered.

Check the dependencies before doing this for real

Disabling a clustered index also disables its table’s nonclustered indexes and related indexed-view indexes. Foreign keys into and out of the table are disabled too. Rebuilding the index does not automatically validate and enable every foreign key. Review those constraints separately, with validation, before declaring the table restored to service.

A disabled clustered index requires an offline rebuild. Dropping it is a different operation that changes the physical design, so I would not use DROP as a casual repair shortcut.

Here is my original demonstration video:

These earlier experiments explain the ENABLE syntax error and the distinction between clustered and nonclustered indexes:

• SQL SERVER – How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’.
• SQL SERVER – Enable and Disable Index Non Clustered Indexes Using T-SQL
• SQL SERVER – Enabling Clustered and Non-Clustered Indexes – Interesting Fact
• SQL SERVER – Enable and Disable Index Non Clustered Indexes Using T-SQL

If the unexpected error says an index is disabled, check that condition before troubleshooting the SELECT itself.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.