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]

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

serge (sergea@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Dan Guzman]

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

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" <sergea@nospam. ehmail.comwrote in message
news:9nNeh.6044 3$4A.1105486@wa gner.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]

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

Thanks Guys, it is much clearer now for me.