Nakul Vachhrajani (9/13/2010)
Emphasis is therefore on the fact that if we have the constraints enabled, SQL does try to use the index which it cannot do because it's disabled.
Actually, the reason you get an error if the index is disabled is that, for a clustered index, the index IS
the table. As a result, disabling the clustered index equates to disabling the table.
If you use a nonclustered rather than a clustered index, then table is still accessible even with the index disabled. You can try this for yourself, by simply changing the "CLUSTERED" to "NONCLUSTERED" in the constraint declaration for the primary key.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis