Sql-server – LEFT vs CHARINDEX for matching the start of a string in T-SQL

performancesql servert-sql

I want to match the beginning of a string in a WHERE clause. While I recognise that so often database-performance is dependant on the underlying data structure, is there a best-practice for doing this? Is there one option which always outperforms the rest?

I want to avoid LIKE as I assume it will be less efficient than LEFT or CHARINDEX. As far as I can tell, my choices are below. Other suggestions welcome:

DECLARE @MyField as varchar(10)
SET @MyField = 'HELLOWORLD'
SELECT 1 WHERE @MyField LIKE 'HELLO%'
SELECT 2 WHERE LEFT(@MyField, 5) = 'HELLO'
SELECT 3 WHERE CHARINDEX('HELLO', @MyField) = 1

Kind Regards,

Jase.

Best Answer

The biggest factor that I can think of is that LEFT(MyField,5) and CHARINDEX('HELLO',@MyField) won't use an index unless the index matches the expression exactly.

However MyField LIKE 'HELLO%' will use any index with MyField in it.

Related Solutions

Sql-server – Equivalent of CHARINDEX OR IN for ints

You haven't really indicated how this is called. Do you really have a string full of comma separated ints or is that just an artifact of some other aspect of your system?

If the outer part of the environment around this requirement is changeable, then typically in SQL Server 2008 and up, I pass a table-valued parameter containing a list of IDs (perhaps from ADO.NET or whatever) and then just use it as a table inside the procedure - i.e. WHERE value IN (SELECT id FROM @table) or INNER JOIN @table AS t ON t.id = users.t

Sql-server – Using like over charindex to search for pattern

The disadvantage is that you need to escape all characters of special significance in the pattern syntax.

Following your previous question it seems the way you do the escaping affects the cardinality estimates.

Testing on SQL Server 2008/2012

SELECT name
/*Copying into a temp table as on 2012 this table is now in the readonly 
 resource database*/
INTO #T
FROM   master..spt_values

Then

SELECT *
FROM   #T
WHERE  name LIKE '%\%%' ESCAPE '\'

gives an estimated row count of 625.33/627.575. But

SELECT *
FROM   #T
WHERE  name LIKE '%[%]%'

gives an estimated row count of 1.8563/1.86296.

In fact no name values in that table contain a literal % character so the second one appears to be more accurate in this case.

The response to this Connect Item states

We do not have support for precise cardinality estimation in the presence of user defined escape characters. So we probably get a poor estimate and a poor plan. We'll consider addressing this issue in a future release.

Best Answer

Related Solutions

Sql-server – Equivalent of CHARINDEX OR IN for ints

Sql-server – Using like over charindex to search for pattern

Related Question