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:

  • Select Pipe delimited String as Table Rows
  • Change app.config connection string depending on PC
  • How to copy tables from SQL Server to MsAccess in TSQL?
  • Show count in SSRS using SQL Server
  • Delphi 6, ADO, MS database “Date” field is same as ftWideString
  • Use variable with TOP in select statement in SQL Server without making it dynamic
  • 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.

  • Count number of NULL values in each column in SQL
  • Column Indexing and DELETE query performance
  • Grant permission to create a user for any database
  • TSQL sp_executesql
  • SQLBulkCopy Row Count When Complete
  • Population of Visual Studio Database Project data-loading scripts from existing data
  • 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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.