Leaf of a Non Clustered Index

After a small break I am back with indexes again...

Agreed that this post provides a very basic info on indexes, but this one is just a starter for the posts I plan to write in the next 2 weeks.

An entry on a non clustered index would have two parts.

1> Non Clustered index key
2> Row Locator.

If the non clustered index is on a table with no clustered index then the Row locator
is the physical address of the entry or simply called as Row ID.If the non clustered index is on a table with a clustered index then the Row locator is the clustered index key.



Grab a look at above picture. It should give a clear idea on the structure of non clustered index on a table with a clustered index.

In case if the clustered index is not unique, then non clustered index adds a unique
indentifier ( 4 bytes length ) to the clustered index key to uniquely identify the row. Before SQL 2000 non clustered indexes always had the Physical address/Row Pointer/Row id and not clustered index key. From SQL 2k onwards it was a welcome change.Let us see the reason why clustered index keys are part of Non clustered indexes.


> Physical address of a row changes due to page spilts. If non clustered indexes contained Row ID then they should be updated every time a page split happens in the table. Even if there is Re Index on clustered index then non clustered index should be rebuilt as well.This can be serious pain. Thankfully this was changed and it brought many many good things to query tuning. We will see that in the next post.

Just to Summarize, the leaf of a non clustered index on a clustered table contains a clustered index key.

Picture courtesy : http://blogs.developpeur.org/photos/raptorxp/images/43172/original.aspx