[SQL Server]Awesome ALL,SOME &ANY

最近進行SQL Tuning時,深入查看相關執行計畫,發現QO改寫用有趣的陳敘式,馬上又引起我的興趣

從SQL2008開始提供ALL、SOME或ANY這美妙比較判斷陳述式,

這讓開發人員在比較數值上更方便且簡潔,下面我來簡單進行一些效能比較。

Not in
select c2
from tbl5
where c2 not in
(
select c2
from tbl6
)

Logical reads: 4342737+8197  , plan cost: 51.81

Not exists
select c2
from tbl5
where not exists
(
select c2
from tbl6
where tbl6.c2=tbl5.c2
)

Logical reads: 8197+ 73 , plan cost:6.959

ALL
select c2
from tbl5
where c2 != all
(
select c2
from tbl6
)

Logical reads: 4342737+ 8197 , plan cost:51.81

In
select c2
from tbl5
where c2 in
(
select c2
from tbl6
)

Logical reads: 73+ 8197 , plan cost:7.49

Any | Some
select c2
from tbl5
where c2 = Any
(
select c2
from tbl6
)
select c2
from tbl5
where c2 = some
(
select c2
from tbl6
)

Logical reads: 73+ 8197 , plan cost:7.49

結論:

看來ALL, SOME|ANY和 IN , not in效能是相同的,所以原本in和not in的效能問題也同樣會發生在ALL, SOME|ANY,

比較可惜的是只能比較單一欄位,希望後面版本可以看到多欄位比較。

 

參考

ALL (Transact-SQL)

SOME | ANY (Transact-SQL)