Posted by Erland Sommarskog on 12/10/06 09:40

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@sommarskog.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

[Back to original message]