Disabling a Clustered Index Automatically Disables Access To Underlying Table [ SQL Server ]

I learnt an interesting fact of SQL Server today. Disabling a clustered index of a table disables access to the data of underlying table. We will not be allowed to execute any of the statements SELECT, UPDATE, DELETE, INSERT on the underlying table.

If we try to execute any of those statements, SQL Server displays the following error message

Msg 8655, Level 16, State 1, Line 1

The query processor is unable to produce a plan because the index '<name of the index>' on table or view '<name of the table>'' is disabled.

So next time when you see this error message, all you need to do to get rid of the problem is to enable the clustered index created on the table by rebuilding it.

It is also noted that disabling clustered indexes automatically disables all non-clustered indexes and XML indexes associated with the table.

You can read the MSDN article Guidelines for Disabling Indexes for more interesting facts