Avoid index scan with LIKE and a variable

[gregbacchus]

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%'

[Erland Sommarskog]

gregbacchus (gr**********@g mail.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, es****@sommarsk og.se

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