Optional Parameters, "index Seek" Plan

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 (

Solution 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).

Solution 2:

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 = 1And@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.

Solution 3:

See Dynamic Search Conditions in T-SQL.

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

Solution 4:

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>WHERECOALESCE(column, -1) =COALESCE(@value, -1)`