SQL Server 2008：为什么在首先满足另一个逻辑条件时进行表扫描？

Question

考虑下面的代码：

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)

由于变量 @var 设置为 0，因此计算搜索的 case 运算符的结果为 1。在 case 文档中写道，直到第一个 WHEN 为 TRUE 时才会计算它。但是当我查看执行计划时，我发现 tableB 也被扫描了。

有谁知道为什么会发生这种情况？当另一个逻辑条件评估为 TRUE 时，可能有一些方法可以避免第二次表扫描？

Answer 1

因为编译和缓存的计划需要适用于 @var 的所有可能值，所以

您需要使用类似

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 的东西，看起来也没有帮助实际上。它仍然无法为您提供通过文字 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)

计划 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)

计划 http://img193.imageshack.us/img193/3977/executionplan.jpg

RE：评论中的问题。在启用“包括实际执行计划”选项的情况下尝试以下操作。

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) 

然后尝试

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%'

编译的计划看起来像

计划 http://img178.imageshack.us/img178 /3977/executionplan.jpg

实际执行计划将仅显示实际执行的一条路径。

Answer 2

如果 tableB 的行数很少，则表扫描是最快的方法。

动态搜索条件的最佳来源：

Erland Sommarskog 的 T-SQL 中的动态搜索条件

对于如何执行此操作（是否可以使用索引）有很多微妙的影响。如果您使用的是 SQL Server 2008 的正确版本，则只需将OPTION (RECOMPILE) 添加到查询中，运行时的局部变量值将用于优化。

考虑到这一点，OPTION (RECOMPILE) 将采用此代码（其中没有索引可以与这些混乱的 OR 一起使用）：

WHERE
    (@search1 IS NULL or Column1=@Search1)
    AND (@search2 IS NULL or Column2=@Search2)
    AND (@search3 IS NULL or Column3=@Search3)

并在运行时对其进行优化（前提是只有 @Search2 传入了一个值）：

WHERE
    Column2=@Search2

并且可以使用索引（如果您在 Column2 上定义了索引）