Don’t use UPPER in WHERE unless you really need it

~ franklinyamamoto

In SQL Server when I run into a problematic query doing an UPPER on a column in the predicate (WHERE clause), I check the collation scheme of the database.  If you have a Case Insensitive (CI) collation scheme you don’t need to use UPPER. As with most functions applied to a column, it is unsargable (prevents efficient use of an index).  You would see CS (Case Sensitive) in your collation scheme if your database has a case sensitive collation scheme.  By default your install of SQL Server uses a case insensitive collation scheme.

You can view your collation scheme by navigating to your database in SSMS and looking at the properties of the database:

Or  you can query sys.databases to see the collation scheme:

select name, collation_name from sys.databases

The demo query

The demo query is looking up a customer by first name and last name.  It is using the UPPER function to perform a case insensitive lookup.  This database has a CI collation scheme though.

SELECT [CustomerID]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[CompanyName]
,[SalesPerson]
,[EmailAddress]
,[Phone]
,[rowguid]
,[AccountNbr]
,[ModifiedDate]
,[CreatedDate]
FROM [dbo].[CustomerAccount] WHERE UPPER(LastName)=UPPER('Yamamoto') AND UPPER(FirstName)=UPPER('Franklin')

The above query contains an UPPER applied to both the LastName and FirstName columns.  Although the query returned 0 rows, it takes 14 seconds to run.  The database I ran this in has an index on last name and first name but it did not get picked up as a result of applying the UPPER function:

 

Rewritten query

After rewriting the query to remove the UPPER we see the scan go away and are now seeing an index seek on the first name last name index:

SELECT [CustomerID]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[CompanyName]
,[SalesPerson]
,[EmailAddress]
,[Phone]
,[rowguid]
,[AccountNbr]
,[ModifiedDate]
,[CreatedDate]
FROM [dbo].[CustomerAccount] WHERE LastName='Yamamoto' AND FirstName='Franklin'

As a result of the query change the query now executes in less than 1 millisecond.

Final thoughts

You might be using a case sensitive collation scheme in your database.  If that is the case (no pun intended) this might not workout for you.  Some things to consider would be performing an upper on the data as it is inserted/updated into the database.  Another option might be to add a computed column and index it.  The latter always depends on how much pressure you’re under to get a quick 0 code change fix out.