What happens if I disable the clustered index?

You are probably already aware that you can disable an index. This can be handy when you have a large load and the load + re-enabling the indexes (you have to completely rebuild them) is faster than leaving the indexes in place. I’ve had pretty limited occasions where this has helped but it can be a handy trick at times. That said, this is only true for non-clustered indexes. What happens when you disable the clustered index?

CREATE TABLE DisableMe (
Id INT,
Col1 varchar(50),
INDEX ci_DisableMe CLUSTERED (Id)
);
INSERT INTO DisableMe VALUES 
(1,'One')
,(2,'Two')
,(3,'Three');
GO
SELECT * FROM DisableMe;
GO

ALTER INDEX ci_DisableMe ON DisableMe DISABLE;
GO
SELECT * FROM DisableMe;
GO

Msg 8655, Level 16, State 1, Line 20

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

Kind of makes sense right? I mean the clustered index is the table so if it’s disabled there is no table to read from. Also, just so you know, the act of disabling the clustered index automatically disables all of the non-clustered indexes and they can’t be re-enabled until after the CI (clustered index) is re-enabled/rebuilt. Again, no data is available to rebuild the index. And speaking of rebuilding the only way to re-enable the clustered index (i.e. make the table available again) is to rebuild it.

ALTER INDEX ci_DisableMe ON DisableMe REBUILD;
GO
SELECT * FROM DisableMe;
GO

That said, you do have an option to completely remove the CI. You end up with a heap but at least you have access to the data.

DROP INDEX DisableMe.ci_DisableMe;
GO