SQL Server 分組取 Top N 方案效能大比拼：ROW_NUMBER 完勝游標、CROSS APPLY、COUNT

場景：產品表 100 萬行，按城市分組取每組最新 10 筆。實測四種方案效能，ROW_NUMBER() 穩居第一，游標次之，CROSS APPLY 與 COUNT 方案不可用。

測試環境

CREATE TABLE dbo.Products (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50),
    AddTime DATETIME,
    City NVARCHAR(10)
);
-- 建立支援索引
CREATE INDEX IX_Products_City_AddTime ON dbo.Products (City, AddTime DESC) INCLUDE (Name);
-- 插入 100 萬測試數據...

方案效能實測

最佳方案代碼：ROW_NUMBER()

游標方案代碼（次優選）

DECLARE @City NVARCHAR(10);
CREATE TABLE #TopN (Id INT, Name NVARCHAR(50), City NVARCHAR(10), AddTime DATETIME);

DECLARE Cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT DISTINCT City FROM dbo.Products ORDER BY City;

OPEN Cur;
FETCH NEXT FROM Cur INTO @City;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #TopN (Id, Name, City, AddTime)
    SELECT TOP 10 Id, Name, City, AddTime
    FROM dbo.Products
    WHERE City = @City
    ORDER BY AddTime DESC;

    FETCH NEXT FROM Cur INTO @City;
END
CLOSE Cur; DEALLOCATE Cur;

SELECT * FROM #TopN ORDER BY City, AddTime DESC;
DROP TABLE #TopN;

核心優化原則

1. 窗口函數優先：ROW_NUMBER()、RANK()、DENSE_RANK() 針對分組排序優化極佳。
2. 索引匹配：(PartitionCol, OrderCol DESC) INCLUDE (SelectCols) 實現索引順序掃描 + 覆蓋，避免排序運算符。
3. 避免相關子查詢：逐行執行，複雜度 O(N²)。
4. CROSS APPLY 慎用：外表大時退化為 Nested Loops，適用於外表小、內表有高選擇性索引的場景。
5. 游標兜底：無窗口函數支援的舊版本（SQL 2000），或極複雜邏輯無法集合化時。

擴展：分頁 + 分組 Top N 組合

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY City ORDER BY AddTime DESC) AS Rn
    FROM dbo.Products
) T
WHERE Rn <= 10
ORDER BY City, AddTime DESC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;  -- 首頁 50 行