How to Optimize the Use of the "OR" Clause When Used with Parameters (SQL Server 2008)

SQL Server is not very good in optimizing the OR predicates.

Use this:

SELECT  key3
FROM    or_table
WHERE   @key1 = 0
        AND @key2 = 0
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key1 = 0
        AND @key2 <> 0
        AND key2 = @key2
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key2 = 0
        AND @key1 <> 0
        AND key1 = @key1
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key1 <> 0
        AND @key2 <> 0
        AND key1 = @key1
        AND key2 = @key2

SQL Server will look to the values of the variables prior to executing the queries and will optimize the redundant queries out.

This means that only one query of four will be actually executed.

MSSQL 2008 has optimization syntax of condition simplification, here it is

 Where (@key1 =0 OR Key1 =@Key1) AND
      (@key2 =0 OR Key2 =@Key2) option(recompile)

This will optimize usage of constants

Have you tries a table valued function?

CREATE FUNCTION select_func1 (  
    @Key1 int=0,
    @Key2 int=0
)
RETURNS TABLE 
AS RETURN (
    Select key3
    From Or_Table
    Where (@key1 =0 OR Key1 =@Key1) AND
          (@key2 =0 OR Key2 =@Key2)
)


select * from select_func1(1,2)