I’ve been working with SQL Server for many years now, and up until recently, I assumed that rebuilding any clustered index would cause non-clustered indexes to be rebuilt as well, because the non-clustered index includes the clustered index in it.
This assumption is wrong.
On SQL Server 2000, this only used to affect non-unique clustered indexes because the uniquifier might change. I was minding SQL Server 2000 instances for a long while after it was no longer supported by Microsoft, which is why this myth stuck around in my head for so long.
On SQL Server 2005 and higher, non-clustered indexes are not rebuilt when a clustered index is rebuilt.
As my friend Gail Shaw says in this forum post (from 2011, no less!):
It’ll have absolutely no effect on the nonclustered indexes. Nonclustered indexes use the clustered index key as a ‘pointer’ and that doesn’t change in a rebuild.
To summarize:
- A non-clustered index is rebuilt if the clustered index is dropped and recreated. Without a clustered index, the non-clustered indexes will have to refer to the row identifier (RID) in the underlying heap instead.
- A non-clustered index is not rebuilt if a clustered index is rebuilt, on SQL Server 2005 and higher.