SQL assign variable with subquery

问题:

I have a question for following 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

Before the execute, I thought the case part should be null = 1 or (0 > 0), and it will return 0.

But now, I wondering why the 2nd SQL will return 1

回答1:

I will post this as an answer as it is quite large text from Training Kit (70-461):

WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10

Some assume that unless precedence rules dictate otherwise, predicates will be evaluated from left to right, and that short circuiting will take place when possible. In other words, if the first predicate propertytype = 'INT' evaluates to false, SQL Server won’t evaluate the second predicate CAST(propertyval AS INT) > 10 because the result is already known. Based on this assumption, the expectation is that the query should never fail trying to convert something that isn’t convertible.

The reality, though, is different. SQL Server does internally support a short-circuit concept; however, due to the all-at-once concept in the language, it is not necessarily going to evaluate the expressions in left-to-right order. It could decide, based on cost-related reasons, to start with the second expression, and then if the second expression evaluates to true, to evaluate the first expression as well. This means that if there are rows in the table where propertytype is different than 'INT', and in those rows propertyval isn’t convertible to INT, the query can fail due to a conversion error.

回答2:

Just to extend @Giorgi's answer:

See this execution plan: Since @i2 is evaluated first (@i2=1), case when @i2 = 1 or anything returns 1.

See also this msdn entry: https://msdn.microsoft.com/en-us/library/ms187953.aspx and Caution section

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.

It's all related to internal optimization.

回答3:

Just to extend both answers.

From Dirty Secrets of the CASE Expression:

CASE will not always short circuit

The official documentation implies that the entire expression will short-circuit, meaning it will evaluate the expression from left-to-right, and stop evaluating when it hits a match:

The CASE statement  evaluates its conditions sequentially and stops with the
first condition whose condition is satisfied.

And MS Connect:

• CASE / COALESCE won't always evaluate in textual order
• Aggregates Don't Follow the Semantics Of CASE

CASE Transact-SQL

The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible.