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

Question

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 1

Your best bet would be to measure the performance on real production data rather than trying to guess (or ask us). That's because performance can sometimes depend on the data you're processing, although in this case it seems unlikely (but I don't know that, hence why you should check).

If this is a query you will be doing a lot, you should consider another (indexed) column which contains the lowercased first letter of name and have it set by an insert/update trigger.

This will, at the cost of a minimal storage increase, make this query blindingly fast:

select * from table where name_first_char_lower = @firstletter

That's because most database are read far more often than written, and this will amortise the cost of the calculation (done only for writes) across all reads.

It introduces redundant data but it's okay to do that for performance as long as you understand (and mitigate, as in this suggestion) the consequences and need the extra performance.