LessThanDot

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

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:

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:

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.

Related Posts

SQL Server 2008 R2 Service Pack 2 (SP2) has been released. Both the Service Pack…

About the Author

After 12 years as a MS SQL Server consultant and trainer, Axel is now using his data knowledge to implement "Product Information Management" solutions. These implementations are usually but not always linked to E-commerce projects.