T-SQL speed comparison between LEFT() vs. LIKE operator

tsql comparison sql-like
Robert Koritnik picture Robert Koritnik · Oct 31, 2010 · Viewed 15.2k times · Source

I'm creating result paging based on first letter of certain nvarchar column and not the usual one, that usually pages on number of results.

And I'm not faced with a challenge whether to filter results using LIKE operator or equality (=) operator.

select *
from table
where name like @firstletter + '%'

vs.

select *
from table
where left(name, 1) = @firstletter

I've tried searching the net for speed comparison between the two, but it's hard to find any results, since most search results are related to LEFT JOINs and not LEFT function.

Answer

user3810913 picture user3810913 · Jul 7, 2014

"Left" vs "Like" -- one should always use "Like" when possible where indexes are implemented because "Like" is not a function and therefore can utilize any indexes you may have on the data.

"Left", on the other hand, is function, and therefore cannot make use of indexes. This web page describes the usage differences with some examples. What this means is SQL server has to evaluate the function for every record that's returned.

"Substring" and other similar functions are also culprits.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More