Functions can be very powerful, but used in the wrong place in a query they can show some unexpected behavior.

In this post I will be using the AdventureWorks2008R2 database and I will query the Sales.SalesOrderHeader to get all the 2006 OrderDates. A query that doesn’t make much sense but will return some interesting results.

Take the following query:

USE AdventureWorks2008R2;
GO

SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '20060101' AND '20061231';
GO

If we take a look at the execution plan:

We see that there is a complete scan of the Clustered Index which makes sense since there is no index on the OrderDate column.

As the Missing Index Hint suggests, create an index on the OrderDate column:

CREATE INDEX IX_SalesOrderHeader_OrderDate
	ON Sales.SalesOrderHeader(OrderDate);
GO

Executing our first query again in another form:

SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE OrderDate >= '20060101' AND OrderDate <='20061231';
GO

Results in the following Execution Plan:

You see that the query is internally translated to SELECT [OrderDate] FROM [Sales].[SalesOrderHeader] WHERE [OrderDate]<=@1 AND [OrderDate]<=@2> just like the first query using the BETWEEN keyword. The big change is in the execution plan. Since we created an index on the OrderDate column, SQL Server is now using an Index Seek on our index to fetch the results.

Since we are looking for all the dates in 2006, you might want to consider using the YEAR function. The YEAR function returns only the YEAR part of a date(time) value:

SELECT YEAR('20060127 02:15:59')

Results in

So the following query should make sense and is more readable then the former 2:

SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2006;
GO

And looking at the result set it makes sense:

But when we look at the execution plan:

We see that our index isn’t seeked anymore but gets a complete scan. So instead of searching in some 4K rows, SQL Server is scanning more than 30K of rows. This is because SQL Server is applying the function to all of the rows in our Sales.SalesOrderHeader table before it’s compared to our desired value.

Conclusion

Be careful when using functions in the WHERE clause of a query. It’s possible that the function will be applied to all the rows before the filter is applied. Resulting in scans, non used indexes, more I/O, memory consumption and a poor performing query.