T-SQL LIKE using variable and index

Question

If [column1] is indexed, the next query may use index:

SELECT * FROM [table] WHERE [column1] LIKE 'starts%'

If I introduce a variable, the query below will never use index:

DECLARE @starts nvarchar(100)
SET @starts = 'starts%'
SELECT * FROM [table] WHERE [column1] LIKE @starts

I want to implement StartsWith search based on user input and i'm not sure what way to choose:

1. escape user input properly for LIKE so optimizer will be able to pick a plan based on literal

2. use WITH(FORCESEEK)

3. use OPTION (RECOMPILE)

Answer 1

There is another choice you didn't list. You can use the OPTIMIZE FOR option to force the query optimizer to make the correct assumption about the nature of the expected variable values. This seems to match your need very well.

DECLARE @starts nvarchar(100)
SET @starts = 'starts%'
SELECT * FROM [table] WHERE [column1] LIKE @starts
OPTION (OPTIMIZE FOR (@starts = 'mnopq%'))

It's described in more detail in this blog. There is also the MSDN documentation.

Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.