Printed 2017/07/23 08:54AM

Search Argument Case Study: WHERE the year was 2002…

By Jen McCown, 2011/07/18

Content Rating: Beginner, Tips

It’s entirely possible that you’re fiddling with your search arguments too much, and missing out on the boost a good index gives your query.  Here’s a quick demonstration of the power of a well-thought-out search clause on a date column.  For this example we’ll use AdventureWorks (freely available at Codeplex).

Let’s say we want to “SELECT SubTotal, VendorID FROM Purchasing.PurchaseOrderHeader AS POH”  for the year 2002. What’s the best WHERE clause for this statement?  Well, that kind of depends on the indexes, doesn’t it?

This query gives you all the indexes for this table (and lists the indexed and included columns):

SELECT as IndexName
    , I.type_desc
    , IC.column_id
    , IC.is_included_column
FROM sys.indexes I
INNER JOIN sys.index_columns IC ON IC.index_id = I.index_id AND IC.object_id = i.object_id
INNER JOIN sys.columns C ON C.column_id = IC.column_id AND C.object_id = I.object_id
WHERE OBJECT_NAME(I.OBJECT_ID) = 'PurchaseOrderHeader'
    -- AND = 'IX_PurchaseOrderHeader_VendorID'   -- You can also view just one index
ORDER BY I.index_id, is_included_column, IC.column_id; 

There isn’t currently an index that applies to an OrderDate search – we need an index whose first non-included column is OrderDate. Let’s assume we’re in a position to create a covering index for our query…that would look like this:

CREATE NONCLUSTERED INDEX ix_PurchaseOrderHeader_OrderDate
ON [Purchasing].[PurchaseOrderHeader] ([OrderDate])
INCLUDE (SubTotal, VendorID);

Now that we have a helpful covering index, we’re back to the question…what’s the best way to limit our query to just the year 2002?  Here are a few options:

 The first two solutions cause a table scan (rather, a clustered index scan, which amounts to the same thing), and the third causes a nonclustered index scan.  The last solution allows SQL to make use of an appropriate index, and it’s an improvement of over 400% efficiency when compared to any of the other implementations.

Moral: When possible, use datatypes in serach conditions as Codd intended – organic and efficient.

Happy days,
Jen McCown

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.