How is it possible to use Index Seek for LIKE %search-string% case?

Lesha if you add OPTION (RECOMPILE) do you get different plan?

Yra, yes, of course. It is because RECOMPILE query hint forces the query optimizer to recompile the query plan each time. So, for:

EXEC dbo.USP_SAMPLE_PROCEDURE N'%94'

the plan includes Index Scan operation which is the ONLY possible way to resolve the query, in my point of view.

PS: I still don't understand how Index Seek can be used to resolve that query.

---

Alexey