>>Check last message in the http://www.thescripts.com/forum/thread142866.html that confirms Walter's suggestion.
>>
>>I googled on "SQL Server parameter LIKE clause slow" and got several interesting links.
>>
>>You may also post now in SQL Server forum.
>
>In that scenario, the % value was passed as is. In my case, this is added at the end of the value. From what I understand, this is what caused that scenario. In my end, since I removed the TOP clause, I gained the optimized speed. What I have observed is, depending on which content is found on a field that you process with %, you can have unpredicted results. This applies when the values are being parameterized. But, we need that to avoid SQL injection. So, having removed the TOP clause seems to work ok so far. This is the only place in the application I have this approach. And, because the number of results are usually low, I only use the max counter on the For Next loop to make sure I only display the first 100 records.

If you still want to use TOP clause you may use derived tables, e.g.

select top 100 from (select ....) order by ...