Why does this sql query do a key lookup?

sql sql-server query-optimization sql-execution-plan
BC. picture BC. · Jul 3, 2010 · Viewed 13.9k times · Source

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

Answer

Martin Smith picture Martin Smith · Jul 3, 2010

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.

Related questions

Counting DISTINCT over multiple columns

Is there a better way of doing a query like this: SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId FROM DocumentOutputItems) AS internalQuery I need to count the number of distinct items from this table but the distinct is over two …

Read More
How to find out what is locking my tables?

I have a SQL table that all of a sudden cannot return data unless I include with (nolock) on the end, which indicates some kind of lock left on my table. I've experimented a bit with sys.dm_tran_locks …

Read More
What is the optimal way to compare dates in Microsoft SQL server?

I have a SQL datetime field in a very large table. It's indexed and needs to be queried. The problem is that SQL always stores the time component (even though it's always midnight), but the searches are to the day, …

Read More