Difference in performance between LIKE and LEFT | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Difference in performance between LIKE and LEFT

Have table with 15 million rows and clustered primary key on EMAIL column. Consider the following 2 queries: SELECT * FROM CUSTOMERS WHERE EMAIL LIKE ‘ab%’ SELECT * FROM CUSTOMERS WHERE LEFT(EMAIL,2) = ‘ab’ Both queries return the same results, but the first query only takes 1 second while the second query takes almost 1 minute. I though that LIKE was the slowest operator …
Can someone explain why?
The first can make use of the index and will result in a seek, while the second cannot, thus resulting in a scan. LIKE will also be slow, when you do LIKE ‘%ab%’. —
Frank Kalis
Microsoft SQL Server MVP
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Danke, Frank
[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />