[MSSQL] 使用 T-SQL 實作分頁

很多表格會遇上資料有上萬筆資料或者上千萬時的大數據，這時如果還是把所有資料撈出來在分頁的話，會造成使用者頁面讀取時間過久，更會有伺服器壓力倍增的問題，這裡提供一個SQL解決方案。

SQL如下：

DECLARE @PageNum AS BIGINT;
DECLARE @PageSize AS BIGINT;
DECLARE @PageCount AS BIGINT;
SET @PageNum = 3;
SET @PageSize = 10;
SET @PageCount = (	SELECT	COUNT(1)
					FROM	dbo.TBase_Product
					WHERE	Product_State != 9) / @PageSize;

IF @PageNum < 1	SET @PageNum = 1;

WITH Temp AS
(
	SELECT	*,
			ROW_NUMBER() OVER(ORDER BY Product_ID) AS RowNum
	FROM dbo.TBase_Product
)

SELECT	RowNum, @PageCount AS PageCount
FROM	Temp
WHERE	RowNum 
		BETWEEN ((@PageNum - 1) * @PageSize + 1) 
			AND (@PageNum * @PageSize)
ORDER BY Product_ID;

執行結果：

參考：

1. How to return a page of results from SQL?
2. Select Count(*) / Count(1) / Count(欄位名) 的差異