[ACCEPTED]-What is a Bookmark Lookup in Sql Server?-indexing
A bookmark lookup is the process of finding 18 the actual data in the SQL table, based 17 on an entry found in a non-clustered index.
When 16 you search for a value in a non-clustered 15 index, and your query needs more fields 14 than are part of the index leaf node (all 13 the index fields, plus any possible INCLUDE 12 columns), then SQL Server needs to go retrieve 11 the actual data page(s) - that's what's 10 called a bookmark lookup.
In some cases, that's 9 really the only way to go - only if your 8 query would require just one more field 7 (not a whole bunch of 'em), it might be 6 a good idea to INCLUDE that field in the 5 non-clustered index. In that case, the leaf-level 4 node of the non-clustered index would contain 3 all fields needed to satisfy your query 2 (a "covering" index), and thus a bookmark 1 lookup wouldn't be necessary anymore.
Marc
It's a NESTED LOOP which joins a non-clustered index 12 with the table itself on a row pointer.
Happens 11 for the queries like this:
SELECT col1
FROM table
WHERE col2 BETWEEN 1 AND 10
, if you have 10 an index on col2.
The index on col2 contains pointers 9 to the indexed rows.
So, in order to retrieve 8 the value of col1, the engine needs to scan 7 the index on col2 for the key values from 1 to 6 10, and for each index leaf, refer to the 5 table itself using the pointer contained 4 in the leaf, to find out the value of col1.
This article points 3 out that a Bookmark Lookup is SQL Server 2000's term, which is replaced 2 by NESTED LOOP's between the index and the table in 1 SQL Server 2005 and above
From MSDN regarding Bookmark Lookups:
The Bookmark 13 Lookup operator uses a bookmark (row ID 12 or clustering key) to look up the corresponding 11 row in the table or clustered index. The 10 Argument column contains the bookmark 9 label used to look up the row in the table or 8 clustered index. The Argument column also 7 contains the name of the table or clustered 6 index in which the row is looked up. If 5 the WITH PREFETCH clause appears in the 4 Argument column, the query processor has 3 determined that it is optimal to use asynchronous prefetching 2 (read-ahead) when looking up bookmarks 1 in the table or clustered index.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.