I’ve just found out that the execution plan performance between the following two select statements are massively different:

select * from your_large_table
where LEFT(some_string_field, 4) = '2505'

select * from your_large_table
where some_string_field like '2505%'

The execution plans are 98% and 2% respectively. Bit of a difference in speed then. I was actually shocked when I saw it.

I've always done LEFT(xxx) = 'yyy' as it reads well. I actually found this out by checking the LINQ generated SQL against my hand crafted SQL. I assumed the LIKE command would be slower, but is in fact much much faster.

My question is why is the LEFT() slower than the LIKE '%..'. They are afterall identical?

Also, is there a CPU hit by using LEFT()?

sql performance
Edit
Report