3

From a Microsoft Course (presented by a third party) I learned that when a table has a clustered index even non-clustered indexes hit the clustered index.

When there is not a clustered index (Heap) as I understand it Nonclustered indexes reference directly into the pages of the table.

Assuming correct understanding, why do the non-clustered indexes reference the clustered index instead of the underlying data pages of the table directly? It seems like this would be less overhead referencing the table data pages directly.

10

The clustered index IS the table. In the classic phone book analogy where everything is ordered by Lastname, Firstname there's not some other book that the phone book refers to. The Phone book IS the book, and it's in that order.

When you have a clustered index, it contains all the data for all the columns in the table, ordered by the key you pick.

In the absence of a CI, the non-clustered indexes need to refer to something so they refer to the RID (row identifier) which is a surrogate key the engine assigns.

  • I think I understand that, however in the table the data is in the leaf pages of the clustered index. Is it purely because architecturally and conceptually we consider the non-leaf nodes of the clustered index to be the table? I'm implying the clearing up of a misconception around the theory a nonclustered index could refer directly to those leaf nodes vs having to hop over the non-leaf nodes of the clustered index. – Joshua Enfield Nov 20 '14 at 21:43
  • Well the leaf nodes may in fact move if the CI changes...that's a bad practice but it's possible to do. – JNK Nov 20 '14 at 21:46
1

From Why Doesn’t SQL Server Use the RID for Lookups from a Nonclustered Index into a Clustered Table? by Paul Randal:

Actually, that is almost how versions prior to SQL Server 7.0 worked. In prior versions (before 7.0) SQL Server used a volatile RID – which would follow the row on every update. More specifically, when a row was updated in these earlier versions and the row needed to physically move within the clustered index, then all of the nonclustered indexes needed to be updated as well.

Given that you can have 249 nonclustered indexes on each table, the work to update these nonclustered indexes could become terribly expensive (in terms of time, disk space, logging, etc…). As a result, the architecture was changed in SQL Server 7.0 to use the clustering key (instead of this volatile RID) – if the table is clustered.

This is an optimization so that even when a row must be relocated the nonclustered indexes don’t need to be updated (as long as the clustering key value doesn’t change).

There is a similar optimization for heaps (forwarding records). See the article for full details.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.