為什麼這個 UPDATE 計劃中有一個聚合？

鑑於這種

declare @Data table (id int, fact char(1));
declare @Summary table (id int, collected varchar(99));

insert @Data(id, fact)
values
   (1, 'a'),
   (1, 'b'),
   (2, 'c'),
   (2, 'd'),
   (2, 'e');

-- Form a list of unique id values
insert @Summary(id, collected) select distinct id, '' from @Data;

-- Accumulate the fact values into collected
update s
set collected = collected + d.fact
from @Summary as s
inner join @Data as d
   on d.id = s.id;

select * from @Summary;

我曾預料到

id   collected
---  ---------
1    ab
2    cde

但是我得到的是

id   collected
---  ---------
1    a
2    c

第一個fact是id寫的，其他的跳過了。原因從計劃中顯而易見

其中 Stream Aggregate 每經過第一行id；5 行從嵌套循環傳遞給它，並將 2 行傳遞給計算標量。

結果和總體計劃形狀在有和沒有主鍵的情況下是相同的。更改為臨時表或真實表沒有區別。我可以在 SQL Server 2017 和 2019 上重現。

我的問題是將聚合插入計劃的理論基礎是什麼？我最好的猜測是它是萬聖節保護的一種形式。我知道它的目的是防止行越過目前掃描位置並因此被第二次更新。我可以看到，在沒有聚合的情況下，將此處作為@Summary 中的一行應用會被多次觸及。不過，這似乎是萬聖節保護的一個非常廣泛的應用。

我知道有辦法實現這一點 - STRING_AGG 是最明顯的。我真正的案例是圍繞建構 JSON。這只是一個最小的可重現範例。我在這裡的具體問題是關於理解優化器的語義和行為。

這不是萬聖節保護，這是正常的UPDATE語義。

從UPDATE 的文件中查看此警告：

如果 UPDATE 語句包含未指定的 FROM 子句，則該語句的結果是未定義的，即，如果 UPDATE 語句不是確定性的，則每個更新的列出現只有一個值可用。

語句不會像您期望的SET那樣累積* - 它只是將匯總表中的每個符合條件的行更新一次。

由於連接會導致收集的潛在值重複，因此優化器引入了流聚合 - 本質上它按堆的“主鍵”（行定位器，在執行計劃中稱為）進行分組，以便僅獲得inBmk1000的一個值collected每一行。

查看計劃 XML，您可以看到內部唯一的“ANY”聚合用於在 collect 和 fact 的可能值中進行選擇：

<ScalarOperator ScalarString="ANY(@Summary.[collected] as [s].[collected])">
 <Aggregate AggType="ANY" Distinct="false">
   <ScalarOperator>
     <Identifier>
       <ColumnReference Table="@Summary" Alias="[s]" Column="collected" />
     </Identifier>
   </ScalarOperator>
 </Aggregate>
</ScalarOperator>

*當您將變數賦值添加到混合中時，這種累積確實有效，儘管它仍然不受“支持”。這被稱為“古怪的更新”（參考）

喬希的回答（感謝喬希！）讓我想到了決定論。

我可以看到基本用法update table set column = value，當有重複的源行時，必須回答這個問題，源中的哪個匹配值將被寫入目標。最簡單的實現是讓迭代器執行完成。儲存引擎碰巧最後傳遞給查詢的那一行就是寫入目標表的值。由於表中沒有固有順序，最後一行是不確定的。

鑑於這種不確定性，優化器可以自由選擇牠喜歡的任何行。無論應用第一行、最後一行還是任何中間行，都滿足語句的語義。鑑於此，選擇第一行並避免重複後續行的冗餘工作是有意義的。這是一種性能優化，但不是對更新語義的約束，也不是為了確保結果的正確性而存在的。

繼續我的範例，我可以看到優化器如何檢測相同的形狀（使用多個匹配的源值更新）並應用性能優化。這就是微軟如何指定更新語句的實現方式。關係模型沒有任何基礎，這意味著它必須如此。

我想有一個替代實現，優化器可以辨識出有多個匹配的行，並且還有一個正在執行的聚合（set column = column + value）。然後它將執行聚合，而不是注入消除重複的流聚合 (ANY) 的性能優化。當然，這將是對 TSQL 更新規範的更改。我不認為 MS 實施這樣的改變會有價值，因為這種行為可以通過其他構造獲得。

引用自：https://dba.stackexchange.com/questions/259464