1

When creating a non-clustered index, the leaf level will contain a reference to rows in the original table:

  • if the table has clustered index, the leaf level will contain the clustered index key;
  • if the table has no clustered index (a heap), the leaf level will contain a pointer to the physical address of the row in the table;

My question is why the non-clustered index wasn't designed to contain the physical address in both cases (in addition to clustered index key). It could save the reads of the B-Tree for key lookups when clustered index is defined.

4

A reason the physical row locator is not stored in the non-clustered index leaf nodes is because the value would need to be updated whenever the physical row location changes due to page splits, reorgs, etc. Having only the CI key as the unique row locator on non-clustered indexes avoids this maintenance, which would be especially costly on a table with many non-clustered indexes.

Note that this isn't as much of a consideration with heaps because those do not suffer from page splits, although forwarding pointers is a consideration when rows are updated to a larger size.

| improve this answer | |

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.