On SQL Server (2008), if I want to filter a string field that starts with something, what is the best way?

Question

On several SQL queries I need to check if a field starts with a character. There are several ways to do it, which one is better in performance/standard?

I usually use

tb.field LIKE 'C%'

but I can also use

LEFT(LTRIM(tb.Field),1) = 'C'

I know well the uses of each case, but not in terms of performance.

Answer 1

I'd go with the first one LIKE C%, it'll use an index on the field if there is one rather than having to do a full table scan.

If you really need to include the whitespace LTRIM trimming in the query, you could create a persisted computed column with the value LEFT(LTRIM(tb.Field), 1) and put an index on it.

Answer 2

LIKE 'C%' is going to perform better than a LEFT(LTRIM()).

The LIKE predicate can still use a supporting index to get at the data you're looking for. I

However, when SQL Server encounters LEFT(LTRIM(tb.Field), 1) = 'C', the database can't determine what you mean. In order to perform a match, SQL Server must scan every row, LTRIM the data and then examine the first character. The end result is, most likely, a full table scan.