8

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
Paul White
  • 94,921
  • 30
  • 437
  • 687
Bassmanjase
  • 83
  • 1
  • 1
  • 3

1 Answers1

11

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.

ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
Kenneth Fisher
  • 24,307
  • 13
  • 63
  • 116