Consider following piece of code:
declare @var bit = 0
select * from tableA as A
where
1=
(case when @var = 0 then 1
when exists(select null from tableB as B where A.id=B.id)
then 1
else 0
end)
Since variable @var is set to 0, then the result of evaluating searched case operator is 1. In the documentation of case it is written that it is evaluated until first WHEN is TRUE. But when I look at execution plan, I see that tableB is scanned as well.
Does anybody know why this happens? Probably there are ways how one can avoid second table scan when another logical condition is evaluated to TRUE?
Because the plan that is compiled and cached needs to work for all possible values of @var
You would need to use something like
if (@var = 0)
select * from tableA
else
select * from tableA as A
where exists(select * from tableB as B where A.id=B.id)
Even OPTION RECOMPILE
doesn't look like it would help actually. It still doesn't give you the plan you would have got with a literal 0=0
declare @var bit = 0
select * from
master.dbo.spt_values as A
where
1=
(case when 0 = @var then 1
when exists(select null from master.dbo.spt_values as B where A.number=B.number)
then 1
else 0
end)
option(recompile)
Plan http://img189.imageshack.us/img189/3977/executionplan.jpg
select * from
master.dbo.spt_values as A
where
1=
(case when 0 = 0 then 1
when exists(select null from master.dbo.spt_values as B where A.number=B.number)
then 1
else 0
end)
Plan http://img193.imageshack.us/img193/3977/executionplan.jpg
RE: Question in comments. Try the following with the "Include Actual Execution Plan" option enabled.
declare @var bit = datepart(second,GETDATE())%2
print @var
if (@var = 0)
select * from
master.dbo.spt_values --8BA71BA5-3025-4967-A0C8-38B9FBEF8BAD
else
select * from
master.dbo.spt_values as A --8BA71BA5-3025-4967-A0C8-38B9FBEF8BAD
where exists(select null from master.dbo.spt_values as B where A.number=B.number)
Then try
SELECT usecounts, cacheobjtype, objtype, text, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
where text like '%8BA71BA5-3025-4967-A0C8-38B9FBEF8BAD%'
The Compiled Plan will look like
Plan http://img178.imageshack.us/img178/3977/executionplan.jpg
The Actual Execution Plan will show only one path was actually executed though.