Avoid index scan with LIKE and a variable

Hi,
Here's my problem: I want to write a stored procedure that returns all
records from a table that have a certain column starting with given
text. I however find that using LIKE and a variable always causes an
index scan... which is causing performance issues. My table has about
3.5M records.

Below is a test. In query analyser if I look at the execution plan for
the following it will come up as in index scan. However, if i just
hard-code the text it all works fine (index seek).

How can I do this with reasonable speed?
Thanks Greg

DECLARE @.find varchar(50)
SET @.find = 'start'

SELECT TOP 100
*
FROM Test
WHERE
Col1 LIKE @.find + '%'
--Col1 LIKE 'start%'gregbacchus (greg.bacchus@.gmail.com) writes:
> Here's my problem: I want to write a stored procedure that returns all
> records from a table that have a certain column starting with given
> text. I however find that using LIKE and a variable always causes an
> index scan... which is causing performance issues. My table has about
> 3.5M records.
> Below is a test. In query analyser if I look at the execution plan for
> the following it will come up as in index scan. However, if i just
> hard-code the text it all works fine (index seek).
> How can I do this with reasonable speed?
> Thanks Greg
>
> DECLARE @.find varchar(50)
> SET @.find = 'start'
> SELECT TOP 100
> *
> FROM Test
> WHERE
> Col1 LIKE @.find + '%'
> --Col1 LIKE 'start%'

You don't say whether the index on Test.Col1 is clustered or not, and
whether this is the index that is scanned. I would expect that the index
on Test.Col1 is non-clustered, and the scan you see is a clustered index
scan.

When you have the literal, SQL Server knows about the query than you
have the variable. For the variable, SQL Server can only make a standard
assumption. Had the variable instead been a parameter to a stored procedure,
SQL Server would have looked at that value.

The best way out may be to simply use an index hint. You could also use
sp_executesql and pass the variable as a parameter.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp