Very good series on the basics of indexes. Unfortunately, I was not able to read Part 3 as of yet since there seems to be an error on the web page. One comment on Part 2 however regarding the three ways a Clustered Index is used. In particular, the Bookmark Lookup. As Gail states in her article it is a very expensive operation and should be avoided if at all possible, With the advent of SQL 2005 and 2008 there is a very easy way to avoid the classic bookmark lookup and still maintain the narrow selectivity of your original non-clustered index. For example, lets take Gail's example and put a little twist on it and then run the execution plan once again and see the difference. First lets drop her current non-clustered index on ProductNumber:

DROP INDEX AK_PRODUCT_PRODUCTNUMBER

ON PRODUCTION.PRODUCT

GO

Ok, piece of cake. Now, let's recreate it again, this time including the NAME column in her query thus making this a truly COVERED INDEX, Notice that we have not changed the original narrow selectivity of this index.

CREATE UNIQUE NONCLUSTERED INDEX AK_PRODUCT_PRODUCTNUMBER

ON PRODUCTION.PRODUCT (PRODUCTNUMBER)

INCLUDE (NAME)

GO

SELECT ProductID, ProductNumber, Name

FROM production.Product

WHERE ProductNumber = 'HN-1224'

Now, run Gail's query and execution plan again and notice the Bookmark Lookup is gone! Replaced with an Index Seek operation. Now notice how fast that row is returned? Use the INCLUDE columns in your NC indexes whenever you can to fix those expensive Bookmark Lookups using the Clustered Index. The Query Optimizer will thank you for it, trust me 🙂 HTH Travis.