What happens if I disable the clustered index?

1

December 16, 2019 by Kenneth Fisher

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

One thought on “What happens if I disable the clustered index?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: