SQL not optimising my parameterised like query

Question

I have a query like so:

select top(10) * from dbo.myTable where DisplayName like 'farm%'

This results in an index seek on DisplayName, since the wildcard is trailing. However the same is not the case when I do this:

declare @val varchar(200) = 'farm'
select top(10) * from dbo.myTable where DisplayName like @val + '%'

or this:

declare @val varchar(200) = 'farm%'
select top(10) * from dbo.myTable where DisplayName like @val

In these cases SQL falls back on the much slower index scan operation. The value being located is a parameter which is provided at runtime so obviously I can't just use the first query for my purposes.

Is there any way I can:

1. Ensure that SQL index seeks for the value, and
2. Have this logic in a stored procedure, precompiled. The query needs to be fast, so I don't want to have to resort to a method which will force a recompilation for every execution (even though the compilation overhead + index seek is still faster than index scan)

I realise that the presence or absence of the wildcard character will influence the execution plan, but SQL does not appear to understand that the wildcard is always being used, even if you concaternate it to the value, as in query #2.

Answer 1

SQL Server can use a range seek for these queries (in the case that the variable contains a leading wildcard the range just ends up being the whole index).

If it isn't choosing to do so in this case it is likely because the use of the variable means that it does not accurately estimate selectivity. You could try adding the OPTION (RECOMPILE) hint so it takes account of the actual variable value.

Answer 2

I think Martin is correct, as SQL actually did do the index seek when I replaced the select * with only the primary key and DisplayName columns, thus eliminating key lookups.

Furthermore, LIKE 'farm%' was being implemented as DisplayName >= 'farm' AND DisplayName < 'farN', so I decided to forego the LIKE operator entirely.

select top(10) * from dbo.EPFSuppliers with(forceseek) 
where DisplayName >= @str and DisplayName < left(@str,len(@str)-1) + char(ascii(right(@str,1)) + 1)

The forceseek hint is necessary because SQL is estimating the number of rows matched at 729, which is an entirely unrealistic number in my situation.

I believe this method is superior to using wildcards, which would have required preprocessing the string to remove escape characters.