Why does this sql query do a key lookup?

Question

I have a table User with a bunch of indexes. One of them is a unique index on the AccountIdentifier column.

Since this is a unique index, why is a key lookup required in addition to the index seek? The index seek tooltip reports that only one record is returned. I've also tried converting the index to a "unique key" type.

alt text http://s3.amazonaws.com/brandonc.baconfile.com/pitchurs/tmp/capture_2.png

Solution 1

Because it is selecting *.

It uses the non clustered index to locate the row(s) but then needs to go and fetch the data to return.

To avoid the bookmark lookup you would need to make the non clustered index a covering index (ideally by reducing the number of columns in the select list but possible also by adding new columns into the index itself or as included columns)

If you have a clustered index on the table the row locator in the non clustered index will include the clustered index key so it won't need a bookmark lookup to satisfy queries on just the AccountIdentifier and clustered index columns.

Solution 2

Key lookup doesn't mean "look up the key", but "look up the row based on the key".

Solution 3

See these articles and blog posts for some more background info on key lookups / bookmark lookups:

• Showplan Operator of the Week - BookMark/Key Lookup
• Bookmark Lookups by Craig Friedmann
• Bookmark Lookups by Randy Dyess
• Books Online on the Bookmark Lookup Operator
• SQL Server Query Optimization – Remove Bookmark Lookup