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?
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:
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:
lastname
from Person.Contact
where left(Lastname, 1) = 'S'
This can be fixed as:
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.
