What is a bookmark lookup?
As discussed in the previous section, when query searches on a column which is not a part of non-clustered index a lookup is required. As mentioned earlier either you need to lookup on the clustered index or you need to lookup on the heap tables i.e. RID lookup. The old definition for these lookup was called as ‘Bookmark’ look up. If you are seeing SQL plan using SQL 2000 you should see a bookmark lookup in your query plan. In SQL 2000 bookmark loop up used a dedicated iterator to determine whether the table is heap table or index table and change the search logic accordingly. So cutting short index lookup and RID lookup are nothing but types of bookmark lookup.
