page 1:900 is the page address: file 1, page 900. Child pointers inside other pages use exactly this address.
The index is non-unique, so SQL Server appends the clustering key id. Every entry becomes a pair (secondary_id, id), sorted by secondary_id first, then id.
The key is just id. The leaf level is the table itself, so each leaf row is a complete row.
The query fixes only the first half of the key. All entries for secondary_id = 4 sit next to each other in key order, but the query cannot know which id comes first. Here is the whole leaf level laid out in sorted order:
4. Written as a pair, that position is "secondary_id 4, with an id smaller than any real id", which is (4, −∞). −∞ is not stored anywhere. It is notation for "lowest possible value of the unknown column".(4, −∞) is greater than (3, anything) and less than (4,17) and every other real (4, …).In the actual execution plan, the Index Seek's properties show Seek Keys[1]: Prefix: secondary_id = 4. A "prefix" seek on the leading key column is the same operation shown here as (4, −∞).
| id | secondary_id | other columns |
|---|
Same table at production scale: 2,000,000 rows, 20,000 clustered leaf pages, both trees 3 levels deep. Drag the match count and compare logical reads for each plan.
The bars use a log scale. Raw read parity is about 6,700 matches (0.33% of the table). The optimizer costs random lookup I/O higher than sequential scan I/O, so its real tipping point usually lands below that figure.