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.

  • If the non-clustered index didn't contain the key, what reads would you save in the event of a key lookup? Are you considering a case where the key is very large? If so, this is why most best practice articles suggest a narrow key. But even if the key is very wide, if you only have to read the pointer in the non-clustered index, and you need to perform a key lookup, that wide key is still part of the read you have to incur during the key lookup. – Aaron Bertrand Jul 18 '18 at 11:51
  • @AaronBertrand I didn't say it shouldn't contain the key, I said it should contain both the key and the physical address and let the optimizer decide which one to use. – Yevgeni Grinberg Jul 18 '18 at 11:54
  • Well, you have said that now, but you hadn't said that when I commented. – Aaron Bertrand Jul 18 '18 at 11:57
  • @AaronBertrand It's what I meant. Sorry if it wasn't clear from the original post. – Yevgeni Grinberg Jul 18 '18 at 11:59

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.

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.