70-433:Good, Better, Best Index Selection

With SQL Server 2008, you’ve got some additional tricks you can pull off with indexes.  With 2005 you could INCLUDE columns in your index, now with 2008 you can also create indexes with a WHERE clause.  This means you can limit the number of rows in your NONCLUSTERED indexes.

By limiting the number of rows included in your index, you limit the amount of space needed to store the index, plus you can limit the impact of inserts and updates.  Think about it.  If you have data in a table that’s current and historical in nature, but you only read the current data, you could build indexes that only cover the active data.  Updates to historic data will no longer take a hit like they would if you had a traditional index defined.

Let’s dig in to this idea a bit more.  Take the following table:

CREATE TABLE OrderHeader (
  , CustomerID INT NOT NULL
  , SalesPersonID INT NULL
  , CommentDate DATE NULL);

Let’s optimize a query that searches this table by CommentDate and SalesPersonID, it returns the CustomerID and SalesPersonID.  What index would you define on this table given that only 5% of the rows have a non-NULL CommentDate?

You could simply index all three columns.

CREATE INDEX ix_OrderHeader__CommentDate_SalesPersonID_CustomerID ON OrderHeader (
   , SalesPersonID
   , CustomerID );

Yeah, that would speed up your query, but as of SQL 2005, you don’t have to include the CustomerID in the indexed columns.  I would include it in the INCLUDE LIST

CREATE INDEX ix_OrderHeader__CommentDate_SalesPersonID__INC_CustomerID ON OrderHeader (
   , SalesPersonID)
     CustomerID );

That would speed up your query, and cut the maintenance costs, since you are no longer ordering your index on the CustomerID column, you’re simply including it.  But notice I told you only 5% of the rows have a non-NULL CommentDate.

Since we know we’re going to be looking up data in this table using CommentDate, we won’t be searching for NULL CommentDate.  We no longer have to index all the rows, only those with a non-NULL value.

CREATE INDEX ix_OrderHeader__CommentDate_SalesPersonID__INC_CustomerID ON OrderHeader (
, SalesPersonID)
CustomerID )
     CommentDate IS NOT NULL;

This is the perfect place to use a WHERE clause, when we know 95% of the rows don’t need to be indexed.  This is the sort of knowledge you’ll need to pass the 70-433!

If you have any questions, please let me know!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *