Two leaf pages from the same table t, drawn to scale. Every horizontal band is 512 bytes, and 16 bands make the full 8,192 bytes. Switch between them to compare how many rows each kind of index fits into the same 8 KB.
These are the main fields DBCC PAGE prints from the 96-byte header of the selected page. Click a page in the map to select it.
The slots are in key order, but the offsets are not. Rows sit in arrival order, and the slot array alone provides the sorting. A binary search on this page runs over the slots.
| Slot | Offset | Key | Length |
|---|
| Bytes | Field | Size | Value | Purpose |
|---|
Each row is the whole table row, about 790 bytes here. Narrower tables fit more, but every column of every row lives at this level.
Each entry is only (secondary_id, id) plus a few bytes of overhead, 12 bytes in total. The same 8 KB holds about 58 times as many entries, so the whole index is about 58 times smaller than the table.
Finding every id with secondary_id = 431 touches one nonclustered leaf page. Reading those rows' other columns then costs one clustered traversal per row, which is the key lookup.
Usable space is 8,192 − 96 = 8,096 bytes. Each row also costs a 2-byte slot. So rows per page = floor(8,096 ÷ (row bytes + 2)).
Rows larger than 8,060 bytes do not fit in-row. Variable-length data is pushed to row-overflow or LOB pages, and the row keeps a 24-byte pointer in its place. Real pages also hold less than the maximum, because splits leave pages about half full and fill factor reserves free space at rebuild time.