Will SQL Server consistently short circuit in this specific case?

First let me say I'm fully aware sql server does not short circuit evaluate. It 'can' if it sees fit because of the execution plan, but it's best not to guess.

I'm wondering if it can be forced in any specific situations regarding a null - such as a dynamic loading of all customers in this query

declare @customerId int

set @customerId = null
select * from customer where ( (@customerId Is Null) Or CustomerId=@customerId)

CustomerId is a开发者_JAVA技巧 PK (int), not nullable

QUESTION: Does anyone know if the engine will always choose the left hand side in this case or if we would indeed end up checking if CustomerId=null on the right hand side for every row. I'm guessing this isn't guaranteed to work since the right hand side may be 'less selective' but I'm curious if sql server sees the null and knows in every case like this to use the left hand side because of the null statement. I believe this is best done as a case stmt below (if you can better the query below please do!) but Im just curious in this case for learning purposes if anyone is aware of a consistent internal behavior here. It works in all of my cases but its also a primary key, not nullable so that may be why this always works. if its a nullable type then the right hand side may be less selective than the left and we now are dealing with a different situation.

My execution plan in both queries seems to be the same.

anyways - a potentially better way to write this (please enhance it if you can)

declare @customerId  int
set @customerId = null

select * from Customer
where 

case 
    when @customerId is null then 1
end = 1

or 
case 
    when @customerId is not null then @customerId
    else -1
end = CustomerId

The idea here is to have workarounds for dynamic sql - so I just want to make sure I'm aware of all situations.

Thanks!

The main issue here isn't short circuiting.

When SQL Server compiles the batch

declare @customerId int

set @customerId = null
select * from customer where ( (@customerId Is Null) Or CustomerId=@customerId)

It doesn't do any kind of "variable sniffing" that takes account of the preceding assignment statement it just treats the value of the @customerId as unknown.

If you are on (some versions of) SQL Server 2008 you can use OPTION(RECOMPILE) to get it to recompile the statement after the variable is assigned.

I suggest reviewing Dynamic Search Conditions in T-SQL

You could try this:

...WHERE CustomerId = COALESCE(@customerId, CustomerId)

or, if you wish, the 'expanded' version of COALESCE:

...WHERE CustomerId = CASE
                        WHEN @customerId IS NULL THEN CustomerId
                        ELSE @customerId
                      END