Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

Get your SQL Server in shape this Spring with Spotlight on SQL Server


Accidental DBA or DBA We've got You Covered with SQL Diagnostic Manager

Upgrade Strategies for Highly Available SQL Server Environments

Avoiding, Detecting, and Repairing SQL Server Database Corruption

Avoid SQL Server functions in the WHERE clause for Performance

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

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.

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.

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     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Tuesday, September 16, 2014 - 1:49:43 PM - Greg Robidoux Read The Tip

Hi Puru,

the computed column would have to be created on the table in the other database in order to use that approach.


Tuesday, September 16, 2014 - 11:58:46 AM - puru Read The Tip

thanks for the help Greg

But i have one more issue that the funtion that i have to use with the column is in other db and  I think we cannot use database name when creating a computed column.


Monday, September 15, 2014 - 4:06:14 PM - Greg Robidoux Read The Tip

Hi Puru,

Not sure what your UDF does, but if performance is an issue you could create a computed column instead of using the UDF and use a persisted value for that column instead of calculating it every time.



Let me know if this helps.


Monday, September 15, 2014 - 3:28:42 PM - puru Read The Tip

Hey good article ,but what if we dont have an index on that column on whice we are using UDF in where clause .then how can we improve the performace

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


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,

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

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

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. 

Sponsor Information