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?