Optional parameters, “index seek” plan

Question

In my SELECT statement i use optional parameters in a way like this:

DECLARE @p1 INT = 1  
DECLARE @p2 INT = 1  
SELECT name FROM some_table WHERE (id = @p1 OR @p1 IS NULL) AND (name = @p2 OR @p2 IS NULL)

In this case the optimizer generates "index scan" (not seek) operations for the entity which is not most effective when parameters are supplied with not null values.
If i add the RECOMPILE hint to the query the optimizer builds more effective plan which uses "seek". It works on my MSSQL 2008 R2 SP1 server and it also means that the optimizer CAN build a plan which consider only one logic branch of my query.
How can i make it to use that plan everywhere i want with no recompiling? The USE PLAN hint seemes not to work in this case.

Below is test code:

-- see plans  
CREATE TABLE test_table(  
    id INT IDENTITY(1,1) NOT NULL,   
    name varchar(10),    
    CONSTRAINT [pk_test_table] PRIMARY KEY CLUSTERED (id ASC))  
GO  
INSERT INTO test_table(name) VALUES ('a'),('b'),('c')  
GO  
DECLARE @p INT = 1  
SELECT name FROM test_table WHERE id = @p OR @p IS NULL  
SELECT name FROM test_table WHERE id = @p OR @p IS NULL OPTION(RECOMPILE)  
GO  
DROP TABLE test_table  
GO  

Note that not all versions of SQL server will change the plan the way i shown.

Answer 1

The reason you get a scan is because the predicate will not short-circuit and both statements will always be evaluated. As you have already stated it will not work well with the optimizer and force a scan. Even though with recompile appears to help sometimes, it's not consistent.

If you have a large table where seeks are a must then you have two options:

1. Dynamic sql.
2. If statements separating your queries and thus creating separate execution plans (when @p is null you will of course always get a scan).

Answer 2

Look at this article http://www.bigresource.com/Tracker/Track-ms_sql-fTP7dh01/ It seems that you can try to use proposal solution:

`SELECT * FROM <table> WHERE IsNull(column, -1) = IsNull(@value, -1)`

or

`SELECT * FROM <table> WHERE COALESCE(column, -1) = COALESCE(@value, -1)`

Answer 3

See Dynamic Search Conditions in T-SQL.

This explains comprehensively the versions where the RECOMPILE option works and alternatives where it doesn't.

Answer 4

Response to Comment on Andreas' Answer

The problem is that you need two different plans.

• If @p1 = 1 then you can use a SEEK on the index.
• If @p1 IS NULL, however, it is not a seek, by definition it's a SCAN.

This means that when the optimiser is generating a plan Prior to knowledge of the parameters, it needs to create a plan that can fullfil all possibilities. Only a Scan can cover the needs of Both @p1 = 1 And @p1 IS NULL.

It also means that if the plan is recompiled at the time when the parameters are known, and @p1 = 1, a SEEK plan can be created.

This is the reason that, as you mention in your comment, IF statements resolve your problem; Each IF block represents a different portion of the problem space, and each can be given a different execution plan.