Question about clustered index configuration
I'm reading chapter 9, Understanding Indexes, and for the most part it makes sense to me, but there's something that I don't understand.
On p. 322, it shows the flow of retrieval for a non-clustered index over a clustered table. I understand that the non-clustered index entries are searched first, and that each one that matches the query criteria is the key to the clustered index value for that record, and that the second part of the search consists of finding the record itself using the clustered value indexes that the non-clustered search returns.
But what I don't understand is the configuration of the clustered index in the illustration. As an example, the non-clustered index returns the following list:
--------------
| Tim | 102 |
--------------
| Tom | 157 |
--------------
| Tony | 209 |
--------------
Using the first entry in the list (Tim ==> 102) , the first clustered seek is labeled Clustered Seek: 102 and looks like this:
--------------
| Tim | 102 |
--------------
| Fred | 1 |
--------------
| Mike | 56 |
--------------
| Ralph | 74 |
--------------
| Steve | 52 |
--------------
Using the second entry in the (non-clustered results) list, the second clustered seek is labeled Clustered Seek: 157 and looks like this:
--------------
| Tom | 157 |
--------------
| Fred | 1 |
--------------
| Mike | 56 |
--------------
| Ralph | 74 |
--------------
| Steve | 52 |
--------------
My first question regarding the clustered index lists is:
Why are the index values in the lists not in sequential order? I thought that the way the clustered index works, based on the fact that they use B-Trees, was that each node stored its values in sequential order, which is how the index is able to determine where to place records in the subsequent nodes?
My second question is:
Why are Tim (102) and Tom (157) at the top of their respective lists?
Now, using the third entry in the non-clustered list, which is labeled Clustered Seek: 209, that clustered index node looks like this:
--------------
| Tim | 102 |
--------------
| Fred | 1 |
--------------
| Mike | 56 |
--------------
| Ralph | 74 |
--------------
| Tony | 209 |
--------------
My questions here is, why is Tony at the bottom? When this "table" is compared to the other two Clustered Seek tables, the non-clustered keys that are used to trigger those index searches appear at the top of the clustered index nodes.
I understood pretty much everything that I was reading until I saw that graphic, so if someone could explain this to me, that would be greatly appreciated.
Thanks.
|