CODEWITHSUNDEEP
sql-servert-sql
T.J. Crowder
T.J. Crowder

Reputation: 1075209

Any advantage to escaping with LIKE over CHARINDEX for a non-sargable query?

In SQL Server, is there any advantage in terms of performance to use an escaped LIKE clause over CHARINDEX, or to use CHARINDEX over an escaped LIKE clause, when the query is known to be non-sargable anyway? (Assuming full text search isn't enabled.)

For instance, these two WHERE clauses are effectively the same:

WHERE FieldName LIKE '%mum\%ble%' ESCAPE '\'

and

WHERE CHARINDEX('mum%ble', FieldName) > 0

Both look for the text mum%ble (literally) anywhere in the field. Since the LIKE starts with a wildcard, the engine can't use an index for it.

Is there any performance advantage to one of the other? (I can see a usage advantage to CHARINDEX [I don't have to make sure to escape things], but I'm curious about performance.)

I'm curious mostly for recent versions of SQL Server, say 2008+, if it matters.

Upvotes: 1

Views: 350

Answers (1)

Patrick Szalapski
Patrick Szalapski

Reputation: 9439

I tried on one data set and got identical execution plans for both with very similar metrics--the only difference was a truly insignificant increase in the cost of the CHARINDEX version. I am guessing the optimizer treats these in the same way and does the almost same internal comparisons for both.

Upvotes: 1

Related Questions