SQL 使用子查询分配变量

Question

我有一个关于以下 2 个 SQL 的问题:

declare @i1 bit, @b1 bit
declare @i2 bit, @b2 bit
declare @t table (Seq int)
insert into @t values (1)

-- verify data
select case when (select count(1) from @t n2 where 1 = 2) > 0 then 1 else 0 end
-- result 0

select @i1 = 1, @b1 = case when @i1 = 1 or ((select count(1) from @t n2 where 1 = 2) > 0) then 1 else 0 end from @t n where n.Seq = 1
select @i1, @b1
-- result 1, 0

select @i2 = 1, @b2 = case when @i2 = 1 or (0 > 0) then 1 else 0 end from @t n where n.Seq = 1
select @i2, @b2
-- result 1, 1

SQL Fiddle Here

在执行之前，我认为case部分应该是null = 1或(0 > 0)，并且它会返回0。

但是现在，我想知道为什么第二个 SQL 将返回 1

Answer 1

只是为了扩展@Giorgi的答案:

查看此执行计划:由于首先评估 @i2 (@i2=1)，case when @i2 = 1 or everything 返回 1。

另请参阅此 msdn 条目:https://msdn.microsoft.com/en-us/library/ms187953.aspx和注意部分

If there are multiple assignment clauses in a single SELECT statement, SQL Server does not guarantee the order of evaluation of the expressions. Note that effects are only visible if there are references among the assignments.

这一切都与内部优化有关。