Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Avoid SQL Server functions in the WHERE clause for Performance

MSSQLTips author Greg Robidoux By:   |   Read Comments (5)   |   Related Tips: More > Performance Tuning

Problem
SQL Server offers many handy functions that can be used either in your SELECT clause or in your WHERE clause.  For the most part these functions provide complex coding that would be very difficult to get this same functionality without these functions.  In addition to the built in functions you also have the ability to develop your own user defined functions.  When functions are used in the SELECT clause to return uppercase output, a substring or whatever, it doesn't affect performance that much, but when functions are used improperly in the WHERE clause these functions can cause major performance issues.

Solution
When functions are used in the SELECT clause, the function has to be run with each data value to return the proper results.  This may not be a bad thing if you are only returning a handful of rows of data.  But when these same functions are used in the WHERE clause this forces SQL Server to do a table scan or index scan to get the correct results instead of doing an index seek if there is an index that can be used.  The reason for this is is that the function value has to be evaluated for each row of data to determine it matches your criteria. 

Here are some simple statements that show you the affect of using a function in the WHERE clause.  To get a better understanding of how these queries are working we are also getting the query plan. This can be done by hitting Ctrl-M in a query window to turn this function on before running the query.

Example 1
This first example uses the LEFT function to get the first two characters of the email address.  Once this is done each row is evaluated to see if it matches the "As" criteria.  The EmailAddress is indexed, so SQL Server should be able to use an index seek to find the data.

SELECT EmailAddress
FROM person.contact
WHERE left(EmailAddress,2) = 'As'

From the query plan output we can see that this query does an index scan, which means it reviews all rows before returning the results.  This is because of the LEFT function that is being used.

Another version of this same query which will return the same results uses the LIKE clause instead.  This query uses the like clause to get all data that begins with "As".  Since there is an index on the the EmailAddress column SQL Server can do an index seek which is much more efficient then an index scan.

SELECT EmailAddress
FROM person.contact
WHERE EmailAddress like 'As%'

Example 2
Here is another example where the UPPER clause is used to transform the EmailAddress into upper case before evaluating the data.  Again the EmailAddress is indexed.

SELECT EmailAddress
FROM person.contact
WHERE upper(EmailAddress) like 'AS%'

We can see that the query plan for this also does an index scan versus an index seek.

A second version of this query again just uses the LIKE clause to get the same results.

SELECT EmailAddress
FROM person.contact
WHERE EmailAddress like 'AS%'

Again this query does an index seek versus a index scan.

Example 3
Here is another example where the function DateDiff is used.  The function is getting rows where the difference in minutes between the ModifiedDate and the getdate() function is greater then zero.  In addition, column ModifiedDate is indexed.

SELECT ModifiedDate
FROM person.contact
WHERE datediff(minute,ModifiedDate,getdate())>0

This first query is using the function and therefore an index scan has to occur.

In this version we are just doing a straight comparison of ModifiedDate compared to getdate().

SELECT ModifiedDate
FROM person.contact
WHERE ModifiedDate < getdate()

Since we are not using a function this query is using an index seek.

Summary
The data that was used to run these queries was the data in the AdventureWorks database.  This database is quite small compared to most SQL Server installations, so the query time results as well as the overall I/O overhead that may be imposed by doing index scans versus index seeks will be quite different and also probably show some significant improvements in your environment.  Not using functions in the WHERE clause is a simple thing to avoid and can provide big performance gains if use alternative methods.

Next Steps

  • Look for poor performing statements in your databases where scans are occurring to see if functions are being used in the WHERE clause
  • Look for alternative methods for getting the same query results, such as some of the examples have shown


Last Update: 5/3/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, October 14, 2011 - 8:53:14 AM - Kenny Read The Tip

In Example 2:

SELECT EmailAddress FROM person.contact WHERE upper(EmailAddress) like 'AS%'

your alternative is to just remove the upper() function??

I tried that in a database with case sensitive settings. it does NOT give the same results!

==> not a valid alternative.

Do you know any? Postgres, Oracle, .. allow indexes on lower(EmailAddress), MS SQL does not. 


Friday, October 14, 2011 - 9:48:31 AM - Greg Robidoux Read The Tip

Try something like this to see if this works:

SELECT EmailAddress FROM person.contact WHERE EmailAddress like 'AS%' collate SQL_Latin1_General_CP1_CI_AS
 


Thursday, September 20, 2012 - 4:20:11 PM - nikit Read The Tip

SELECT EmailAddress FROM person.contact WHERE EmailAddress like 'AS%' collate SQL_Latin1_General_CP1_CI_AS

IF you use the collate property that will also force the SQL server to use the Scan instead of seek. I currently have that same issue and not sure how to resolve it


Thursday, September 20, 2012 - 5:45:13 PM - Greg Robidoux Read The Tip

@nikit - you are correct.  Changing the collation does a scan on the index if it is case sensitive.

Looks like you don't have much of an option if your database is case sensitive and you need to search the data ignoring the case.

A work around would be to always store the data using the same case or making the column case insensitive in the table if it doesn't really need to be case sensitive.

CREATE TABLE [Person].[Contact2](
   [EmailAddress] [nvarchar](50) collate SQL_Latin1_General_CP1_CI_AS NULL,
   ) ON [PRIMARY]


Wednesday, November 06, 2013 - 4:30:02 AM - aa Read The Tip

brilliant



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.