Performance Issue - No Index Usage If Variables Used.

Hi guys,



My company is currently migrating from Interbase to SQL Server 2005. During the migration we have came across a rather peculiar issue and wondering if anyone can advise.



We have a table.. named "prospect" which holds client information



We have a stored procedure which hangs on the following statement.


DECLARE @surname char(25);

SET @surname='BLAH%';

SELECT *
FROM Prospect
WHERE c_surname LIKE @surname;


The above takes 28 seconds to run. The following statement returns a result inside a second.


SELECT *
FROM Prospect
WHERE c_surname LIKE 'BLAH%';





In Interbase, the original returned the answer within a second too. The schema in both database is the same.



The 1st statement does not use an index! The execution plan is different to the 2nd statement. I am aware I can create an index recommended by the Database Engine Tuning which solves the issue or specify the index to use in the original statement but why does the engine not use the correct index if there is a variable involved? I need to know as we have just started looking at the code.



Thanks,



Kalim