How sequential traversing between intermediate leaves of clustered index used?

Question

Intermediate leaves of clustered index are linked sequentially (next, previous) for faster access (between intermediate nodes) [2], [3], etc.:

How this access is used?
Why is it needed?

[2]
Clustered Index Structures
http://msdn.microsoft.com/en-us/library/ms177443.aspx
[3]
Clustered Tables vs Heap Tables
http://www.mssqltips.com/tip.asp?tip=1254

Answer 1

The clustered index (and not non-clustered indices) can be used for range queries. Do you know what that is ? Horizontal traversal of the B-Tree enhances the speed of navigating the CI when determining qualified rows during range queries.

In a more general sense, if the server cache is too small, and the CI pages get paged out, when any query (not only the range queries) need to get the next page when walking down, or sideways, through a CI, it can get the page with a single disk access, because the pages are linked by a pointer; ie. it avoids walking back up one level to find the next page). Just one of the many reasons CIs are much faster than NCIs; they are far more enhanced because the NCI depends on them (your other question today).

The diagram has mistakes (contains false info), or to put it more precisely, it is a descriptive, non-technical diagram, from a non-technical corporation:

1. The intermediate levels have a single pointer to the page at the next level (not multiple pointers).

2. The leaf level IS the data row. There are no pointers to rows (at the intermediate OR leaf level).

3. The Index Pages do not resemble a page of text and images. Each Index Page contains hundreds of index B-Tree entries.

4. The Root page is different only in that the first entry is the single root to the index ; it contains hundreds of entries which are of course second level, and may be third level, etc.

There is a reason technicians draw, and use, technical drawings: among others, it avoids misunderstandings and confusion. No questions re my technical diagram ?