建立百萬筆測試表 (33 秒),編寫通用分頁預存程序 dbo.im531_Page,支援動態資料表、欄位、條件、排序,兩種演算法可切換:BETWEEN / TOP。
CREATE PROCEDURE dbo.im531_Page
@TabeName NVARCHAR(50),
@Fields NVARCHAR(1024),
@SearchWhere NVARCHAR(1024),
@OrderFields NVARCHAR(1024),
@pageNumber INT, -- 每頁筆數
@page INT -- 頁碼 (1-based)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlType TINYINT = 0; -- 0=BETWEEN, 1=TOP
DECLARE @sql NVARCHAR(MAX);
IF @SearchWhere <> '' SET @SearchWhere = ' WHERE ' + @SearchWhere;
IF @page < 2 SET @page = 1;
IF @page = 1
SET @sql = 'SELECT TOP ' + CAST(@pageNumber AS NVARCHAR(20)) + ' ' + @Fields
+ ' FROM [' + @TabeName + '] [a] WITH(NOLOCK) ' + @SearchWhere
+ ' ORDER BY ' + @OrderFields;
ELSE
BEGIN
-- CTE 產生行號
SET @sql = ';WITH [Page_____Table] AS ('
+ 'SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderFields + ') AS [RowNow], ' + @Fields
+ ' FROM [' + @TabeName + '] [a] WITH(NOLOCK) ' + @SearchWhere + ')';
IF @sqlType = 0 -- BETWEEN
SET @sql = @sql + ' SELECT * FROM [Page_____Table] WITH(NOLOCK)'
+ ' WHERE [RowNow] BETWEEN ' + CAST((@page-1)*@pageNumber+1 AS NVARCHAR(20))
+ ' AND ' + CAST(@page*@pageNumber AS NVARCHAR(20))
+ ' ORDER BY [RowNow]';
ELSE -- TOP
SET @sql = @sql + ' SELECT TOP ' + CAST(@pageNumber AS NVARCHAR(20)) + ' *'
+ ' FROM [Page_____Table] WITH(NOLOCK)'
+ ' WHERE [RowNow] > ' + CAST((@page-1)*@pageNumber AS NVARCHAR(20))
+ ' ORDER BY [RowNow]';
END
EXEC (@sql);
END
兩種演算法皆穩定在 **毫秒級** (1–10 ms),WITH(NOLOCK) 避免鎖定、ROW_NUMBER() 単次掃描、CTE 物化行號後直接區間查找。SQL Server 2000 不支援,2008/2012/2014/2016/2019 同樣適用。
關鍵字:分頁、預存程序、ROW_NUMBER、CTE、百萬資料、毫秒級、NOLOCK