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:

escape user input properly for LIKE so optimizer will be able to pick a plan based on literal
use WITH(FORCESEEK)
use OPTION (RECOMPILE)

because of variable the second query compiles in some generalized way. I read that stuff somewhere on the internet — svolkov
– svolkov, Commented Jan 16, 2015 at 20:04
While focused more on parameterization than local variables, this dba post will probably be at least a bit useful: dba.stackexchange.com/questions/33698/… — Jason Whitish
– Jason Whitish, Commented Jan 16, 2015 at 21:14

hatchet - done with SOverflow · Accepted Answer · 2015-01-16 20:19:07Z

4

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.

edited Jan 16, 2015 at 20:19

answered Jan 16, 2015 at 20:07

hatchet - done with SOverflow

16.3k5 gold badges49 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

svolkov Over a year ago

i have to properly escape user input inside OPTIMIZE FOR statement anyway, right?

M.Ali Over a year ago

I have just tested this on AdventureWorks2008 and it doesn't work :( . QUERY and EXECUTION PLANS

svolkov Over a year ago

i tested and it works, however i don't see any advantage before the 1st choice

hatchet - done with SOverflow Over a year ago

@svolkov - I don't understand your question about escaping user input. Can you include an example?

hatchet - done with SOverflow Over a year ago

@svolkov - The literal is just there to direct the query optimizer. You would just come up with the literal once when you write your stored proc. It doesn't really matter what the literal is, except for where you place the '%' in it, which is the whole point. You could use the same literal 'mnopq%' that I used. Are you saying that the parameter coming in might be 'hello%' one time, and '%world' the next time? That was not how I read your question, since you say you're writing a "starts with".

|

Collectives™ on Stack Overflow

T-SQL LIKE using variable and index

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related