為什麼 OFFSET … FETCH 與舊式 ROW_NUMBER 方案之間存在執行計劃差異？

SQL Server 2012 中引入的新OFFSET ... FETCH模型提供了簡單和快速的分頁。考慮到這兩種形式在語義上相同且​​非常常見，為什麼會有任何差異？

人們會假設優化器可以辨識兩者並（微不足道地）最大限度地優化它們。

這是一個非常簡單的案例，OFFSET ... FETCH根據成本估算，它的速度快了約 2 倍。

SELECT * INTO #objects FROM sys.objects

SELECT *
FROM (
   SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
   FROM #objects
) x
WHERE r >= 30 AND r < (30 + 10)
   ORDER BY object_id

SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

可以通過創建 CI 或添加過濾器來改變此測試案例，object_id但不可能消除所有計劃差異。OFFSET ... FETCH總是更快，因為它在執行時做的工作更少。

問題中的範例並不完全產生相同的結果（該OFFSET範例有一個錯誤）。下面更新的表單修復了該問題，刪除了ROW_NUMBER案例的額外排序，並使用變數使解決方案更通用：

DECLARE 
   @PageSize bigint = 10,
   @PageNumber integer = 3;

WITH Numbered AS
(
   SELECT TOP ((@PageNumber + 1) * @PageSize) 
       o.*,
       rn = ROW_NUMBER() OVER (
           ORDER BY o.[object_id])
   FROM #objects AS o
   ORDER BY 
       o.[object_id]
)
SELECT
   x.name,
   x.[object_id],
   x.principal_id,
   x.[schema_id],
   x.parent_object_id,
   x.[type],
   x.type_desc,
   x.create_date,
   x.modify_date,
   x.is_ms_shipped,
   x.is_published,
   x.is_schema_published
FROM Numbered AS x
WHERE
   x.rn >= @PageNumber * @PageSize
   AND x.rn < ((@PageNumber + 1) * @PageSize)
ORDER BY
   x.[object_id];

SELECT
   o.name,
   o.[object_id],
   o.principal_id,
   o.[schema_id],
   o.parent_object_id,
   o.[type],
   o.type_desc,
   o.create_date,
   o.modify_date,
   o.is_ms_shipped,
   o.is_published,
   o.is_schema_published
FROM #objects AS o
ORDER BY 
   o.[object_id]
   OFFSET @PageNumber * @PageSize - 1 ROWS 
   FETCH NEXT @PageSize ROWS ONLY;

該ROW_NUMBER計劃的估計成本為0.0197935：

該OFFSET計劃的估計成本為0.0196955：

這節省了0.000098 個估計成本單位（儘管OFFSET如果您想為每一行返回一個行號，該計劃將需要額外的運算符）。OFFSET一般來說，該計劃仍然會稍微便宜一些，但請記住，估計成本正是如此——仍然需要進行實際測試。兩個計劃中的大部分成本是輸入集的全部類型的成本，因此有用的索引將使兩種解決方案都受益。

在使用常量字面值的情況下（例如OFFSET 30在原始範例中），優化器可以使用 TopN 排序而不是後跟 Top 的完整排序。當 TopN 排序所需的行是常量字面量且 <= 100（ 和 的總和OFFSET）FETCH時，執行引擎可以使用不同的排序算法，該算法的執行速度比廣義 TopN 排序更快。這三種情況總體上具有不同的性能特徵。

至於為什麼優化器不自動轉換ROW_NUMBER語法模式來使用OFFSET，有很多原因：

1. 編寫一個匹配所有現有用途的轉換幾乎是不可能的
2. 自動轉換一些分頁查詢而不是其他的可能會令人困惑
3. 不能保證該OFFSET計劃在所有情況下都更好

上述第三點的一個範例發生在分頁集非常寬的情況下。與使用或掃描索引相比，使用非聚集索引查找所需的鍵並手動查找聚集索引會更有效。如果分頁應用程序需要知道總共有多少行或頁面，還有其他問題需要考慮。這裡有另一個關於“key seek”和“offset”方法的相對優點的很好的討論。OFFSET``ROW_NUMBER

OFFSET總的來說，如果合適的話，在徹底測試之後，人們做出明智的決定來改變他們的分頁查詢使用可能會更好。

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