SQL Server. why UPPER function uses index scan

Question

Query #1

SELECT ID 
FROM Product 
WHERE Name ='Banana'

Query #2

SELECT ID 
FROM Product 
WHERE UPPER(Name) = 'Banana'

I am investigating execution plans for the above queries. I understood that 1st query uses index seek but why 2nd query uses index scan? Is it because of UPPER function ?

Yes, any function on the where condition doesn't use index, Try to avoid function on where condition column — Nayanish Damania, Mar 07 '20 at 19:05
UPPER and LOWER functions are not SARGABLE so they can not use the index. May persisted computed columns will help to overcome this issue. — Esat Erkec, Mar 07 '20 at 19:07
If your column is not case sensitive, you might want to look into case insensitive collations — James Z, Mar 07 '20 at 19:16
[What makes a SQL statement sargable?](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) — Lukasz Szozda, Mar 07 '20 at 19:26
For sql server the comparison for strings doesnt take into account the case, so if you use upper or not its going to give you the same result. — Rishi, Mar 08 '20 at 07:36

Thom A · Accepted Answer · 2020-03-07T20:28:53.583

Applying a function to a column usually causes a query to be "non-SARGable", this means that indexes can no longer be used (there are some very few exceptions). The key reason this happens is because the order of the data is no longer the same, due to the use of the function (note, just because a function doesn't change the order doesn't mean it'll be SARGable). As the order of the new data may not reflect that of the index, it can't be used

The obvious reason that you are using UPPER is because you have Case Sensitive collation. Now, for some collations, the order of letters are sorted as A,a,B,b,C,c...Y,y,Z,z, while on others it's A,B,C...Y,Z,a,b,c...,y,x this means that depending on the collation, a could be both greater or less than B. Applying UPPER to that would mean that the value is always less than B; thus changing the order.

A common method, therefore, for Case Sensitive collation databases is to store a PERSISTED computed column with the upper case value:

ALTER TABLE dbo.YourTable ADD Name_UC AS UPPER([Name]) PERSISTED;

Then, when you pass values (parameters) you also make sure they are all in Upper case:

SELECT {columns}
FROM dbo.YourTable
WHERE Name_UC = UPPER(@YourParameter);

Of course, for what you have (WHERE UPPER(Name) = 'Banana') if you are using a Case Sensitive Collation, it will never return any results, as 'anana' are lower case letters.

SQL Server. why UPPER function uses index scan

1 Answers1