Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Common SQL Server Mistakes – Functions in the WHERE Clause

This continues my series on Common SQL Server mistakes, looking at more T-SQL mistakes.

What’s Wrong?

If you saw a query like this, would you see a problem?

select
  o.OrderID
  , o.CustomerID
  , o.Qty
from Orders o
where datepart( yyyy, o.OrderDate) = '2010'

If there are 1,000 orders in this table, there probably isn’t an issue. But if there are 1,000,000, then this is an issue.

Why? Let’s examine the execution plan:

This table has 1000 rows in it, but it doesn’t use indexing to find those orders that were placed in 2010. Instead it scans all rows. The reason is that the function being used in the WHERE clause means that the index cannot be used.

Instead, what you would want to do is write the query like this:

select
  o.OrderID
  , o.*
  , o.Qty
from [OrderItems] o
where o.OrderDate >= '20100101'

In this way, we eliminate the function from the WHERE clause and allow the query optimizer to take advantage of the indexes on the column OrderDate.

You see similar issues with queries like:

select
lastname
from Person.Contact
where left(Lastname, 1) = 'S'

This can be fixed as:

select
lastname
from Person.Contact
where Lastname like 'S%'

Basically you want to move the function away from the column and put it on the other side of the comparison so that indexes can be used.

Too often we have developers writing queries like this, assuming that the functions are efficient. They are, but when they are executed against every row in a table, an index can’t be used for seek operations, which are always quicker than scans for any significant data set.

When you are writing queries, do your best to avoid functions against columns in your tables. Instead try to rework the query to move the function. An alternative that I’ll blog about another time is computed columns.

Comments

Posted by Dukagjin Maloku on 27 October 2010

Nice and simple warning! Nice explanation, Steve, thnx for sharing.

Posted by jcrawf02 on 27 October 2010

check this out, Jeremiah found an interesting way around this if you needed to use it

facility9.com/.../a-simple-refactoring-functions-in-the-where-clause

Posted by Steve Jones on 27 October 2010

That's a nice technique Jeremiah has. Personally I'd recalculate things and have a set variable in the WHERE clause, but either work.

Leave a Comment

Please register or log in to leave a comment.