Optional Parameters In Stored Procs - CASE vs. OR

Question

Is there any difference Performance wise between these filter methods?

Method 1: WHERE (@Col1 IS NULL OR t.column = @Col1) 


Method 2: WHERE 1 = case when @col1 is null then 1 else case when col1 = @col1 then 1 else 0 end end

Answer 1

Why not use Coalesce?

Where Col1 = Coalesce(@Col1, Col1)

EDIT: (thx to Joel's comment below) This works only if col1 does not allow Nulls, or if it does allow nulls and you want the null values excluded when @Col1 is null or absent. SO, if it allows nulls and you want them included when @Col1 parameter is null or absent then modify as follows:

 Where Coalesce(@Col1, Col1) Is Null Or Col1 = Coalesce(@Col1, Col1)

Answer 2

If you know your Col1 column doesn't itself contain any null values, you can do this:

WHERE Col1 = COALESCE(@Col1, Col1)

Otherwise your CASE statement should typically do a little better than the OR. I add emphasis to "typically" because ever table is different. You should always profile to know for sure.

Unfortunately, typically the fastest way is to use dynamic sql to exclude the condition from the query in the first place if the parameter is null. But of course save that as an optimization of last resort.

Answer 3

Yes. CASE has a guaranteed execution order while OR does not. Many programmers rely on OR short-circuit and are surprised to learn that a set oriented declarative language like SQL does not guarantee boolean operator short-circuit.

That being said using OR and CASe in WHERE clauses is a bad practice. Separate the condition into a clear IF statement and have separate queries on each branch:

IF @col1 IS NOT NULL
  SELECT ... WHERE col1 = @col1;
ELSE
  SELECT ... WHERE <alternatecondition>;

Placing the condition inside the WHERE usually defeats the optimizer that cannot guess what @col1 will be and produces a bad plan involving a full scan.

Update

Since I got tired of explaining again and again that boolean short-circuit is not guaranteed in SQL, I decided to write a full blog column about it: SQL Server boolean operator short-circuit. There you'll find a simple counter example showing that boolean short-circuit is not only not guaranteed, but relying on it can actually be very dangerous as it can result in run time errors.