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 '\'
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.
One Solution collect form web for “Any advantage to escaping with LIKE over CHARINDEX for a non-sargable query?”
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.