Avoid enclosing Indexed Columns in a Function in the WHERE clause

By Bill Graziano on 12 September 2007 | Tags: Query Tuning

When a function is wrapped around an indexed column in the WHERE clause it will usually prevent the proper usage of that index. This article gives an example and discusses what other options are available.

I still remember the first time I ever saw this truly crush a server.  I was doing performance tuning at a client site.  We still hadn't convinced their developers that the DBA's needed to review their T-SQL code before it went into production.  One morning we noticed performance problems on the server.  Disk reads would go way, way up.  SPIDs would begin to block.  Response time on their web site slowed to a crawl -- if it responded at all.

After first insisting that no new database code was rolled out they finally admitted they'd changed one line of one WHERE clause in a single stored procedure.  That change brought the database server to a stop.  They had added a YEAR function around an indexed date column so that a report would only pull back a single year.  That changed the query plan enough to start generating table scans.

A script to simulate this looks like this:

USE AdventureWorks
GO
IF  EXISTS (SELECT * FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') 
			AND name = N'IX_SalesOrderHeader_OrderDate')
	DROP INDEX [IX_SalesOrderHeader_OrderDate] ON [Sales].[SalesOrderHeader] 
GO
CREATE INDEX IX_SalesOrderHeader_OrderDate ON Sales.SalesOrderHeader(OrderDate)
GO

SELECT	SalesOrderID,
	OrderDate
FROM	Sales.SalesOrderHeader
WHERE	YEAR(OrderDate) = 2003
AND	MONTH(OrderDate) = 7


SELECT	SalesOrderID,
	OrderDate
FROM	Sales.SalesOrderHeader
WHERE	OrderDate BETWEEN '7/1/2003' AND '7/31/2003'
GO


IF  EXISTS (SELECT * FROM sys.indexes 
			WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') 
			AND name = N'IX_SalesOrderHeader_OrderDate')
	DROP INDEX [IX_SalesOrderHeader_OrderDate] ON [Sales].[SalesOrderHeader] 
GO

This script creates an index on OrderDate.  In my sample queries I'm only pulling back a single month.  The first query has a cost of 0.080 and does a scan of the index.  The second query has a cost of 0.004 and does a seek on the index.  Yes, the costs are very low because I don't have a large set of data.  The first query still has a cost twenty times higher than the second.

When you wrap a function around an indexed column SQL Server must compute the value of the function for each row in the table.  When you just compare the indexed column to a scalar value or the result of a function then SQL Server can use that value to seek into the index.  The only time you can use a function around an indexed column is if you use an indexed computed column that matches the function you're using.

This type of coding is mostly used for date fields when computing ranges such as querying for the previous week.  Hopefully this little tip will save you from making the same mistake those developers did.