問題描述
我對以下 2 個 SQL 有疑問:
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
在執行之前，我認為case部分應該是null = 1 or (0 > 0)，它會返回0.
Before the execute, I thought the case part should be null = 1 or (0 > 0), and it will return 0.
但是現在，我想知道為什么第二個 SQL 會返回 1
But now, I wondering why the 2nd SQL will return 1
推薦答案
我會將此作為答案發布，因為它來自 Training Kit (70-461):
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
有些人認為除非優先規則另有規定，否則謂詞將從左到右評估，短路將在可能的情況下進行.換句話說，如果第一個謂詞propertytype = 'INT' 評估為 false，SQL Server 不會評估第二個謂詞 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.
然而，現實是不同的.SQL Server 可以內部支持短路概念；然而，由于一次性概念在語言中，不一定會按從左到右的順序計算表達式.它可以決定，基于成本相關的原因，從第二個表達式開始，然后如果第二個表達式的計算結果為真，則計算第一個表達式也是如此.這意味著如果有行屬性類型不同于INT"的表，并且在這些行中propertyval 不能轉換為 INT，查詢可能由于轉換錯誤.
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.
這篇關于SQL 使用子查詢分配變量的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！