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?


Show source
| sql-server   | performance   | indexing   | sql   | sql-server-2005   2017-01-04 01:01 1 Answers

Answers ( 1 )

  1. 2017-01-04 01:01

    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).

◀ Go back