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

Question

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

Answer 1

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.