A Simple Refactoring – Functions in the WHERE Clause

Putting functions in the where clause of a SQL Statement can cause performance problems. If you aren’t careful, these problems can add up and bring a powerful production system to its knees. For this example, I’m using the Northwind database, but you could do this on any database. The first thing to do is put an index on the OrderDate column:CREATE INDEX IX\_Orders\_OrderDate ON dbo.Orders (OrderDate) ;Take a look at this first query:SELECT COUNT(\*) FROM dbo.Orders AS o WHERE DATEADD(MM, 2, o.OrderDate) < '1997-05-04' ;[caption id="" align=“alignnone” width=“956”] Scanning the stars… or something.[/caption] This returns very quickly on the Northwind database because we have such a small volume of data, but a query like this could cause a lot of problems in production. Why? Well, it’s doing a full table scan. Don’t believe me? Take a look a the execution plan over there. The database is forcing a table scan because of the use of the DATEADD function on an indexed column. SQL Server will have to compute the value of every OrderDate plus 2 months in the database and then compare it to May 4th, 1997. What happens if we flip this around? What if we change our query so that we’re doing the math on our input value of May 4th, 1997?``` SELECT COUNT(*) FROM dbo.Orders AS o WHERE o.OrderDate < DATEADD(MM, -2, ‘1997-05-04’) ;