SQL Server 查詢結果動態加行號：三種方案效能與適用性深度比較

常見需求：查詢結果需顯示行號，但不想在應用層處理。本文比較三種 T-SQL 方案的原理、效能、優缺點，給出選型建議。

方案一：自聯結計數法（單一 SQL，效能最差）

USE pubs
SELECT COUNT(*) AS Rank, a1.au_lname, a1.au_fname
FROM authors a1, authors a2
WHERE a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
GROUP BY a1.au_lname, a1.au_fname
ORDER BY Rank

• 原理： 以字串連接比較大小，統計「大於等於自己」的筆數即排名。
• 缺點： 1) 自聯結產生笛卡兒積，複雜度 O(N²)；2) 相同值產生並列排名（如 1,2,2,4），缺號。

方案二：IDENTITY + 暫存表（多語句，效能佳）

USE pubs
SELECT IDENTITY(INT, 1, 1) AS Rank, au_lname, au_fname
INTO #tmp
FROM authors
SELECT * FROM #tmp
DROP TABLE #tmp

• 原理： SELECT INTO 配合 IDENTITY 函數自動產生流水號。
• 優點： 掃描一次，O(N) 複雜度，無並列排名問題。
• 缺點： 需多條語句、建立/刪除暫存表，有輕微 DDL 開銷。

方案三：ROW_NUMBER() 視窗函數（SQL 2005+ 首選）

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

• 優點： 單一語句、標準 SQL、支援 PARTITION BY 分組編號、效能最佳、無暫存表開銷。
• 適用： SQL Server 2005 及以上版本。

效能與選型總結表

最佳實踐建議

1. SQL Server 2005+ 直接使用 ROW_NUMBER()，語法簡潔、效能最優。
2. 舊版本 (2000) 採用 IDENTITY + #Temp 方案。
3. 若行號僅供前端顯示，強烈建議在應用層產生，避免資料庫層額外運算與網路傳輸擴大。