Which is better: Bookmark/Key Lookup or Index Scan

Question

I know that an index seek is better than an index scan, but which is preferable in SQL Server explain plans: Index seek or Key Lookup (Bookmark in SQL Server 2000)?

Please tell me they didn't change the name again for SQL Server 2008...

Answer 1

Index seek, every time.

Lookups are expensive, so this is covering indexes and especially the INCLUDE clause was added to make them better.

Saying that if you expect exactly one row, for example, a seek followed a lookup can be better than trying to cover a query. We rely on this to avoid yet another index in certain situations.

Edit: Simple talk article: Using Covering Indexes to Improve Query Performance

Edit, Aug 2012

Lookups happen per row which is why they scale badly. Eventually, the optimiser will choose a clustered index scan instead of a seek+lookup because it's more efficient than many lookups.

Answer 2

Key lookup is very similar to a clustered index seek (pre 2005 SP2 was named 'seek with lookup'). I think the only difference is that the Key Lookup may specify an additional PRE-FETCH argument instructing the execution engine to prefetch more keys in the cluster (ie. do a clustered index seek followed by scan).

Seeing a Key Lookup should not scare you. Is the normal operator used in Nested Loops, and Nested Loops is the run-of-the-mill join operator. If you want to improve a plan, try improving on the join and see if it can use a merge join instead (ie. both sides of join can provide rows on the same key order, fastest join) or a hash-join (have enough memory for the QO to consider a hash join, or reduce the cardinality by filtering rows before the join rather than after).