Which is better: bookmark / keyword search or index scan

Question

I know that an index is better to look for than a scan index, but which is preferable in the plans for explaining SQL Server: index search or key search (bookmark in SQL Server 2000)?

Please tell them not to change the name for SQL Server 2008 ...

Answer 1

The index strives every time.

Hacks are expensive, so this covers indexes, and the INCLUDE clause is especially added to make them better.

Saying that if you expect exactly one row, for example, searching after a search might be better than trying to span a query. We rely on this to avoid another index in certain situations.

Edit: simple discussion article: Using coverage indexes to improve query performance

Edit, August 2012

The search takes place in a string, so they do not scale well. In the end, the optimizer will choose a clustered index scan instead of search + search, because it is more efficient than many search queries.

Answer 2

Key lookup is very similar to a clustered index search (pre 2005 SP2 was called "search with search"). I think the only difference is that Key Lookup can specify an additional PRE-FETCH argument instructing the execution mechanism to pre-fetch more keys in the cluster (i.e., Search for a clustered index followed by a scan).

Watching a key search should not scare you. It is the usual operator used in nested loops, and nested loops are the union operator. If you want to improve the plan, try to improve the connection and see if it can use merge join instead (that is, both sides of the join can provide strings in the same key order, the fastest join) or hash join (enough memory for QO to consider a hash join or reduce power by filtering strings before merging, not after).

Answer 3

This SO question indicates that key searches are something to avoid. Index distortion will certainly be more effective.