為什麼多對一合併連接會改變數據集的排序順序？

我有一個計算row_Number(). 該表在與行號的分區和排序相同的列（和順序）上具有聚集索引。

使用合併連接（多對一）時，即使聚集索引的順序正確，也需要排序。刪除連接也會刪除排序操作。

應該為 row_number 計算提供動力的聚集索引：

create clustered index [ClusteredIndex_e060df3fbf464a8eb9b6ea5d46a9a5f5] on [dbo].[log1]
(
   [client] asc,
   [orderId] asc,
   [campaign] asc,
   [id] asc,
   [DateStamp] asc
)

create clustered index [ClusteredIndex_dd0ee53e050d436cba2cab7c678a39e5] on [dbo].[LiveReference]
(
   [client] asc,
   [orderId] asc,
   [campaign] asc
)

查詢：

   with cr as 
(
   select distinct client, orderId,campaign
   from LiveReference
)

select e.[DateStamp]
 ,e.[campaign]
 ,e.[client]
 ,e.[orderId]
 ,e.[ad]
 ,e.[id]
 ,e.[source]
,row_number() over (partition by e.[client] ,
                       e.[orderId] ,
                       e.[campaign] ,
                       e.[id] 
                   order by e.[DateStamp]) as num
from [dbo].[log1] e 
inner join cr on 
                       e.client = cr.client
                       and e.campaign = cr.campaign
                       and e.orderId =  cr.orderId

這給出了以下計劃：

刪除連接也會刪除排序：

select e.[DateStamp]
 ,e.[campaign]
 ,e.[client]
 ,e.[orderId]
 ,e.[ad]
 ,e.[id]
 ,e.[source]
,row_number() over (partition by e.[client] ,
                       e.[orderId] ,
                       e.[campaign] ,
                       e.[id] 
                   order by e.[DateStamp]) as num
from [dbo].[log1] e 

（我知道這也刪除了連接執行的過濾，但這並不能解釋為什麼排除這些行會改變順序）

為什麼排序連接的結果會出現不正確的順序？

一般來說，merge join（包括merge join concatenation）只保留join key的排序順序。

合併連接鍵是client, campaign, orderId. 視窗函式所需的輸入排序順序是client, orderId, campaign, id , datestamp。

因此，合併連接無法提供視窗函式所需的排序順序。您可以避免使用嵌套循環連接進行排序（例如使用提示）。

我在使用 Merge Join Concatenation 避免排序中寫了詳細資訊。

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