When will a LIKE expression use an index?

[rlowson]

I have a stored procedure that is taking a longer than is reasonable to execute, and I have isolated the part that is costing me the most. It is the part that uses a LIKE expression. Here is the sub query in question:

@partial_vernac ular is the stored procedure parameter that contains the text I want to match on.

DECLARE @searchlike nvarchar(100);
SET @searchlike = @partial_vernac ular +'%';

SELECT * FROM dbo.ls_vernacul ar_word_item_oc currances
WHERE word LIKE @searchlike

I have read that if a LIKE query uses the form WHERE term LIKE 'mytext%', that should prompt use of an index. Will that work in this situation where the text is passed in as a parameter? I can't see that this behaves any differently performance wise than when I set my search text this way:

SET @searchlike = '%' + @partial_vernac ular +'%';

The [word] field is defined as varchar(50). Does the field data type have any bearing on this?

[ck9663]

Is your [WORD] column in your index?

~~ CK

[rlowson]

Yes, the table does have an index on the column in question. The table contains about 100,000 records, and since recently adding the LIKE expression to the procedure, I have lost about half a second to a second (this is part of an auto-suggestion feature, which is why the speed is important to me).