14 September 2007 08:30 Alex_Kuznetsov

Yet another index covering tip

Suppose you want you NCI index cover a query. Suppose you want to make sure that a column, let's say CustomerID, is stored in the index.
Even if your table is currently clustered on CustomerID, it is still a good practice to explicitly include CustomerID in your NCI. The reason is simple: sometimes we can drop the CI and build another CI on another column(s). Your index should still store CustomerID, so that it still covers your query.

Comments

# re: Yet another index covering tip

19 September 2007 10:49 by simonsabin

I assume you mean use the INCLUDE clause and not just include the column in the keys of the index as that is over kill.

Obviously this isn't possible with SQL2000 and below in which case I would say, droppping and recreating a clustered index should be assessed for impact as it will have a huge impact on your query performance and should only be done whilst assessing ALL queries that access the system.

# re: Yet another index covering tip

23 September 2007 20:24 by Alex_Kuznetsov

Hi Simon,

Actually I did not mean INCLUDE clause. I meant 2000 as well as 2005 and I don't think I'm suggesting an overkill. Suppose my Customers table is currently clustered on CustomerID. Suppose I have a NCI on (SignUpDate). Supoose I have a query

SELECT <list of columns from Orders table only>

FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID

WHERE o.SignUpDate BETWEEN '20070101' AND '20070201'

At that time my index on (SignUpDate) already covers the query. Note that another index on (SignUpDate, CustomerID) would be essentially the same, of the same size.

However, suppose some time later I want to change my clustering index from CustomerID to (PhoneNUmber, DOB). At that time my index on (SignUpDate) will no longer be coveriny for the query I provided above. However, the index on (SignUpDate, CustomerID) will still be covering.

My point is quite simple: if you explicitly specify columns which you need for index covering, including bookmark columns, you index will be essentially the same, but it will allow you to switch to another CI faster.