Does rebuilding my clustered index also rebuild my non-clustered indexes?

construction

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:

  1. 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.
  2. A non-clustered index is not rebuilt if a clustered index is rebuilt, on SQL Server 2005 and higher.

Photo by 贝莉儿 NG on Unsplash

%d bloggers like this: