SQL SERVER 2008: WHY TABLE SCANNING WHEN ANOTHER LOGICAL CONDITION IS SATISFIED FIRST?

Ankita Pandey

Total Post:183

Points:1285
Posted by  Ankita Pandey
 1100  View(s)
Ratings:
Rate this:
Hi Expert!

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?

Thanks in advance!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: SQL Server 2008: Why table scanning when another logical condition is satisfied first?

    Hi Ankita!


    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)

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!