SQL Using a String Function on Indexed Column Performance

Question

I have a general question regarding using String Functions and their impact on performance. I have a table with a non-clustered index on column ID. The column has 20 digit varchar in it. When I run :

SELECT col1, col2 
FROM tbl
WHERE ID = '00000000009123548754'

The result comes back very fast. But when I run

SELECT col1, col2 
FROM tbl
WHERE RIGHT(ID, 10) = '9123548754'

It takes a very long time. The estimated execution plan for the first query has a Index Seek, where as for the second query is has a Index Scan.

I understand that the Seek as oppose to the Scan is the reason one is faster, but why does the String Function Right() has such an impact?

If there are 20 digits, then the second will return no rows. — Gordon Linoff
– Gordon Linoff, Commented Jan 4, 2017 at 0:22
Well, RIGHT() cannot work in conjunction with the hashed values your index is based upon, so it has to evaluate every ID value. Try and run Database Engine Tuning Advisor against your query and see what it comes up with. — uncoder
– uncoder, Commented Jan 4, 2017 at 0:25

geofftnz · Accepted Answer · 2017-01-04 00:33:41Z

4

The reason there is a difference is because the RIGHT(ID,10) has to be resolved for every row in order to filter it (hence the scan), whereas the ID = '00000000009123548754' clause can be resolved to a simple seek.

In technical terms, your first query is sargable, while the second isn't (due to the function).

answered Jan 4, 2017 at 0:33

geofftnz

10.1k2 gold badges46 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Using a String Function on Indexed Column Performance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related