問(wèn)題描述
我對(duì)以下 2 個(gè) SQL 有疑問(wèn):
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
在執(zhí)行之前，我認(rèn)為case部分應(yīng)該是null = 1 or (0 > 0)，它會(huì)返回0.
Before the execute, I thought the case part should be null = 1 or (0 > 0), and it will return 0.
但是現(xiàn)在，我想知道為什么第二個(gè) SQL 會(huì)返回 1
But now, I wondering why the 2nd SQL will return 1
推薦答案
我會(huì)將此作為答案發(fā)布，因?yàn)樗鼇?lái)自 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
有些人認(rèn)為除非優(yōu)先規(guī)則另有規(guī)定，否則謂詞將從左到右評(píng)估，短路將在可能的情況下進(jìn)行.換句話說(shuō)，如果第一個(gè)謂詞propertytype = 'INT' 評(píng)估為 false，SQL Server 不會(huì)評(píng)估第二個(gè)謂詞 CAST(propertyval AS INT) >10 因?yàn)榻Y(jié)果是已經(jīng)知道.基于這個(gè)假設(shè)，期望是查詢應(yīng)該永遠(yuǎn)不會(huì)失敗嘗試轉(zhuǎn)換不是敞篷車(chē).
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.
然而，現(xiàn)實(shí)是不同的.SQL Server 可以內(nèi)部支持短路概念；然而，由于一次性概念在語(yǔ)言中，不一定會(huì)按從左到右的順序計(jì)算表達(dá)式.它可以決定，基于成本相關(guān)的原因，從第二個(gè)表達(dá)式開(kāi)始，然后如果第二個(gè)表達(dá)式的計(jì)算結(jié)果為真，則計(jì)算第一個(gè)表達(dá)式也是如此.這意味著如果有行屬性類型不同于INT"的表，并且在這些行中propertyval 不能轉(zhuǎn)換為 INT，查詢可能由于轉(zhuǎn)換錯(cuò)誤.
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.
這篇關(guān)于SQL 使用子查詢分配變量的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！