DB分頁處理時除了回傳總筆數的需求，有時候還得回傳資料在整體資料中的序號，雖然我們用了Offset Fetch，但還是可以和ROW_NUMBER函式結合，透過ORDER BY NOTHING(NULL)，用相同的值來替代排序，SQL查詢只多吃一點可以忽略的成本，來試試。
第三篇，取資料序號
- 取總筆數的效能(COUNT(1) OVER() VS CTE)
- 排序條件選擇
- 取資料序號(ROW_NUMBER() OVER(ORDER BY NOTHING))
- Offset Fetch Vs Row_number(總筆數及資料序號)
這次的實驗中，我們還是用AdventureWorks2014資料庫中的PRODUCTION.TRANSACTIONHISTORY資料表作DB分頁測試，假設需求想要多個ROW_NO欄位。
實驗
測試時順便要比較加上row_number函式會增加多少資源:
- 第1次語法是沒有加上row_numer的查詢
- 第2次則是加上row_number，但order by nothing(放null放1都行)
- 第3次的語法則是加上row_number，但order by 的條件和原本分頁的條件相同。
查詢1:
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 10;
SELECT *
FROM PRODUCTION.TRANSACTIONHISTORY
ORDER BY ProductID
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY
查詢2:
DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 10;
SELECT *,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ROW_NO
FROM PRODUCTION.TRANSACTIONHISTORY
ORDER BY ProductID
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY
查詢3:
DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 10;
SELECT *,ROW_NUMBER() OVER(ORDER BY ProductID) AS ROW_NO
FROM PRODUCTION.TRANSACTIONHISTORY
ORDER BY ProductID
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY
執行結果: 查第10頁，每頁10筆，因此資料序號是91-100。
CPU、執行時間及邏輯讀取:
| 執行方式 | CPU Time | 經過時間 | 邏輯讀取 |
| 沒有Row_Number() | 0ms | 49ms | 317 |
| Row_Number(order by Nothing) | 0ms | 49ms | 317 |
| Row_Number(order by ProductID) | 0ms | 50ms | 317 |
執行計畫:
第1次查詢沒有Row_number的查詢
第2次與第3次查詢的執行計畫相同，雖然多了row_number()的查詢，只多了一點點的成本
成本分析:
| 執行方式 | 子樹成本 |
| 沒有Row_Number() | 0.316297 |
| Row_Number(order by Nothing) | 0.316307 |
| Row_Number(order by ProductID) | 0.316307 |
小結
- I/O相同，多了ROW_NUMBER()後，執行計畫也多2個步驟，成本只多了一些些，經過時間也相同。
- WEB程式端也可以使用每頁筆數和頁數推算。
- 下一篇測試直接用ROW_NUMBER()作分頁處理與OFFSET FETCH作分頁處理的比較。
參考
Stackoverflow ROW_NUMBER Without ORDER BY
[SQL Server][Pagination]DB端的分頁處理(一)取總筆數的效能(COUNT(1) OVER() VS CTE)
