Nonclustered indexes and select operations

When you select a row using a nonclustered index, the search starts at the root level. syspartitions stores the page number for the root page of the nonclustered index (stored in sysindexes in Adaptive Server versions earlier than 15.0).

In Figure 5-8, “Green” is greater than “Bennet,” but less than “Karsen,” so the pointer to page 1007 is followed.

“Green” is greater than “Greane,” but less than “Hunter,” so the pointer to page 1133 is followed. Page 1133 is the leaf page, showing that the row for “Green” is row 2 on page 1421. This page is fetched, the “2” byte in the offset table is checked, and the row is returned from the byte position on the data page.

Figure 5-8: Selecting rows using a nonclustered index

Image showing how you select rows from a nonclustered index. On the left are the root pages, or the right are the data pages. The selection is show by a series of arrows tracing the path through the pages to the eventual data pages.