Northwind - execution plan bug? Why Index Seek and no Bookmark Lookup?

[serge]

If you display the execution plan and run the following:

SET STATISTICS IO ON
go

SELECT ProductID, SupplierID
FROM Products
WHERE SupplierID = 1
I don't understand how come there is no
Bookmark Lookup operation happening to get the
ProductID?

I only see an Index Seek happening on SupplierID.
There is no composite index SupplierID + ProductID
so what am I not understanding here?

Thank you

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

SELECT ProductID, SupplierID
FROM Products
WHERE SupplierID = 1
I don't understand how come there is no
Bookmark Lookup operation happening to get the
ProductID?

I only see an Index Seek happening on SupplierID.
There is no composite index SupplierID + ProductID
so what am I not understanding here?

In a non-clustered index, there needs to be a row locator to get to
the data page. If the table does not have a clustered index, the row
locator is an internal record id. But if the table has a clustered index,
the row locator is simply the clustered index key. As a result of this,
the columns of the clustered index are present in the leaf node of
every non-clustered index.

And thus a query like the one above can be evaluated from the non-clustered
index alone.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

To add to Erland's response, an index with leaf nodes that contain all
columns needed by a query is a 'covering' index. Besides the clustered
index key columns, one can add non -key columns in a SQL 2005 non-clustered
by specifying an INCLUDE column list in order to cover particular queries.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <se****@nospam.ehmail.comwrote in message
news:9n********************@wagner.videotron.net.. .

If you display the execution plan and run the following:

SET STATISTICS IO ON
go

SELECT ProductID, SupplierID
FROM Products
WHERE SupplierID = 1
I don't understand how come there is no
Bookmark Lookup operation happening to get the
ProductID?

I only see an Index Seek happening on SupplierID.
There is no composite index SupplierID + ProductID
so what am I not understanding here?

Thank you

[serge]

Thanks Guys, it is much clearer now for me.