筆記－T-SQL 分頁查詢並傳回總筆數

資料庫查詢結果要做分頁，我較常用的做法是拉到 C# 端將物件陣列或 List<T> 存入 MemoryCache，用 .Length 可以取總筆數，用 Skip(pageSize  * (pageNo - 1)).Take(pageSize) 取回指定頁數資料，換頁或排序時從 MemoryCache 讀取以求迅速並減輕資料庫伺服器負擔，遇到變更查詢條件或按查詢鈕時再重新查詢資料庫。

最近遇到的案例，因使用者較多、單筆資料量也大，擔心 Cache 消耗過多記憶體，決定改用 T-SQL 實做分頁同時取得總筆數，過去少有機會練習，試作之餘寫篇筆記備忘。

我找到較簡潔的做法是組裝查詢條件先轉成 CTE，用 Count(1) OVER () 計算總筆數放在每筆資料第一欄（重複資料會浪費空間，但既然會做分頁筆數不會太多，耗損可忽略），再使用 OFFSET ＋ FETCH NEXT 子句實現類似 LINQ Skip() 與 Take() 的效果，程式範例如下：參考來源

DECLARE @pageSize INT, @pageNo INT;

SET @pageSize = 25;

SET @pageNo = 3;

;WITH T

AS (

    SELECT *

    FROM Production.Product

    WHERE ListPrice > 10

    )

SELECT TotalCount = COUNT(1) OVER (), T.*

FROM T

ORDER BY ProductNumber OFFSET(@pageNo - 1) * @pageSize ROWS

FETCH NEXT @pageSize ROWS ONLY;

2017-08-24 補充：回應網友 Ken 提問：CTE 並非絕對必要，寫成 CTE 的好處是形成標準範本，應用於不同場合時只需置換 CTE 內的查詢語法，其他部分不動，更容易寫成共用程式模組。

拿 AdventureWorks 資料庫 Production.Product 練兵，執行可得總筆數 291 筆，每頁 25 筆取第 3 頁，查得 25 筆：

關於 OFFSET 與 FETCH 的詳細介紹，可參考 德瑞克：SQL Server 學習筆記- SQL Server 2012 ：分頁處理：認識 OFFSET 和 FETCH 子句。

OFFSET 跟 FETCH 是 SQL 2012 才加入的新指令，如果你的資料庫還停在滿清時代 SQL 2005 或 SQL 2008，就只能回歸使用 ROW_NUMBER() 配合 BETWEEN 分頁，但配合 CTE 使用，查詢稍稍複雜，違和程度尚在可忍受範圍。 （但如果不用 CTE，而是同樣查詢條件 Copy and Paste 就會讓人想吐了…）程式範圍如下：參考來源

DECLARE @pageSize INT, @pageNo INT;

SET @pageSize = 25;

SET @pageNo = 3;

;WITH T

AS (

    SELECT ROW_NUMBER() OVER (ORDER BY ProductNumber) AS RowNo,*

    FROM Production.Product

    WHERE ListPrice > 10

    ),

T2 AS (

    SELECT COUNT(1) TotalCount FROM T

)

SELECT *

FROM T2, T

WHERE RowNo BETWEEN (@pageNo - 1) * @pageSize  + 1 

AND @pageNo * @pageSize;

執行結果與 OFFSET + FETCH 版本相同：