When a small number of rows are requested by a query, the SQL Server optimizer will try to use a non-clustered index on the column or columns contained in the WHERE clause to retrieve the data requested by the query.

If the query requests data from columns not present in the nonclustered index, then the SQL Server must go back to the data pages to get the data in those columns. In the above scenario, if a table has clustered index, it is called bookmark lookup (or key lookup); if the table does not have clustered index, but in a heap, it is called RID lookup.