SQL Server 動態列號生成三大技法：臨時表、自聯接、視窗函數完整比較

場景需求

查詢結果集需動態添加行號，支援分頁、報表排序、快取過濾。

技法一：臨時表 + IDENTITY（最高效能，不適用視圖）

SET NOCOUNT ON;

SELECT IDENTITY(INT, 1, 1) AS RowOrder,
       au_lname, au_fname
INTO #tmp
FROM authors;

SELECT * FROM #tmp;
DROP TABLE #tmp;

• ✅ 效能最佳，單次掃描
• ✅ 可建立索引進一步加速後續查詢
• ❌ 無法用於視圖、內嵌函數
• ❌ 需額外 DDL 權限

技法二：自聯接計數（適用視圖，大資料集效能差）

SELECT 
    RANK = COUNT(*),
    a1.au_lname,
    a1.au_fname
FROM authors a1
INNER JOIN authors a2 
    ON a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
GROUP BY a1.au_lname, a1.au_fname
ORDER BY COUNT(*);

執行結果範例

RANK  au_lname        au_fname
1     Bennet          Abraham
2     Blotchet-Halls  Reginald
3     Carson          Cheryl
...

• ✅ 可用於視圖、函數、單一陳述式
• ❌ O(N²) 複雜度，百萬筆以上不可用
• ❌ 重複值導致排名不連續
• ❌ 需聯合唯一鍵欄位避免誤判

技法三：ROW_NUMBER() 視窗函數（現代標準，SQL Server 2005+）

SELECT 
    ROW_NUMBER() OVER (ORDER BY au_lname, au_fname) AS RowNum,
    au_lname, au_fname
FROM authors;

• ✅ 效能優異，單次掃描 + 排序
• ✅ 支援 PARTITION BY 分組編號
• ✅ 可用於視圖、CTE、子查詢
• ✅ 語法標準、可讀性最佳

效能對比實測 (10 萬筆資料)

分頁應用範例

DECLARE @PageIndex INT = 2, @PageSize INT = 20;

WITH Numbered AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RowNum,
        OrderID, CustomerID, OrderDate, TotalAmount
    FROM Orders
)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Numbered
WHERE RowNum > @PageIndex * @PageSize 
  AND RowNum <= (@PageIndex + 1) * @PageSize;

選型建議決策樹

1. SQL Server 2005+ 且無特殊限制？→ ROW_NUMBER() (首選)
2. SQL Server 2000 或需物理化結果供多次查詢？→ 臨時表 + IDENTITY
3. 必須在視圖/函數中使用且資料量 < 10,000？→ 自聯接 (最後手段)
4. 需分組編號 (如各客戶訂單內部排序)？→ ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate)

自聯接技法僅具歷史參考價值，現代開發應全面採用視窗函數。臨時表適合中繼結果物理化場景。