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
The request processor was unable to provide a plan,
because the "ci_DisableMe" index in the table or view "DisableMe" is disabled.

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