Learn T-SQL Querying

Lookups

When a non-clustered index is used to locate rows, only the index columns are present at the leaf level of the index. If there are additional columns required from the underlying data pages because they are referenced in the SELECT list or elsewhere in the query, an additional step is required to retrieve this data. The leaf level of the non-clustered index contains a pointer to the data row, which must be followed in order to retrieve the rest of the data in the row. This operation is called a lookup.

The format of the pointer in the non-clustered index depends on the underlying table storage. For heaps, we store a row ID, which is made up of the file ID, page ID, and slot ID (a slot is where the row is stored on the page) of the ...