What happens if you turn off the clustered index?

You probably already know that you can disable the index. This can be handy with large loads, and loading + enabling indexes (you will have to completely rebuild them) is faster than loading with enabled indexes.

I know of few cases where this has helped, but sometimes it can be a handy trick. I should say that this is only true for unclusterized indexes.

And what happens if you disable a 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

That’s right, right? I mean, a clustered index is a table, so if you disable an index, there is no way to read it.

Also, just so you know, the action to disable a clustered index will disable all non-clusterized indexes, and they cannot be re-enabled until the CI (clustered index) is enabled/rebuilt.

Again, there is no data available to rebuild an unclusterized index. And the only way to re-enable a clustered index is to rebuild it (i.e. make the table available again).

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

However, you have the option of completely removing the CI. You get a bunch, but at least you still have access to the data.

DROP INDEX DisableMe.ci_DisableMe;
GO

WISSENSCHAFTSCOACHING: BERATUNG, COACHING, LEKTORAT, ÜBERSETZUNG