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
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Danke, Frank

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>