"John Bailo" wrote:
What is a SQL server bookmark (SQL2000/w2k standard)?
Are you referring to bookmark lookups in your execution plans? If so, then
bookmark lookups occur when data is read from a table for one section of the
query, and then a subsequent section of the query requires more information
for the returned records. It therefore has to go back to the table and lookup
the extra information using the clustered index returned from the first
section of the query. This degrades performance.
For example, using Northwind, execute the below SQL after selecting 'Show
execution plan' in Query analyser:
SELECT c.CompanyName, o.OrderDate
FROM Customers c
INNER JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE c.CustomerID='ALFKI'
In the execution plan you can see that the optimizer had to do a bookmark
lookup on the Orders table because the first part of the query (bottom right
of the plan) used the CustomerId non-clustered index, which only returned the
CustomerId for the where clause. The optimizer then had to go back and look
up the OrderDate column for each of the retuned records because it is listed
in our select statement.
If you add a new non-clustered covering index on the Orders table with the
CustomerId and OrderDate columns then this will bypass the bookmark lookup
because the optimizer already gets the OrderDate value for each record from
the non-clustered index:
CREATE INDEX [IX_Orders_CustomerId_OrderDate] ON [dbo].[Orders]
([CustomerID], [OrderDate])
HTH
