SQL Server 2005 引入 NTILE() 與 ROW_NUMBER() 視窗函數，徹底解決分頁效能與寫法難題，無需再羨慕 Oracle ROWNUM。

方法一：NTILE() 等高分桶分頁

DECLARE @rows_per_page INT = 20;
DECLARE @current_page  INT = 2;
DECLARE @total_pages   INT;

-- 計算總頁數
SELECT @total_pages = COUNT(*) / @rows_per_page
FROM testtable;
IF (COUNT(*) % @rows_per_page) > 0 SET @total_pages = @total_pages + 1;

-- NTILE 將資料平均分成 @total_pages 桶
WITH t AS (
    SELECT NTILE(@total_pages) OVER (ORDER BY id) AS page_number, *
    FROM testtable
)
SELECT * FROM t
WHERE page_number = @current_page;

缺點：需先計算總頁數 (兩次掃描)，資料分布不均時頁面筆數不固定。

方法二：ROW_NUMBER() 精確分頁 (推薦)

DECLARE @rows_per_page INT = 20;
DECLARE @current_page  INT = 2;
DECLARE @start_row     INT = (@current_page - 1) * @rows_per_page;

WITH t AS (
    SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_number, *
    FROM testtable
)
SELECT * FROM t
WHERE row_number BETWEEN @start_row + 1 AND @start_row + @rows_per_page;

優點：單次掃描、精確控制每頁筆數、支援任意排序、易擴展總筆數查詢。

通用分頁存儲過程範本

CREATE PROCEDURE sp_PageQuery
    @TableName      NVARCHAR(128),
    @Fields         NVARCHAR(MAX) = '*',
    @OrderBy        NVARCHAR(200),
    @WhereClause    NVARCHAR(MAX) = '',
    @PageSize       INT = 20,
    @PageIndex      INT = 1,
    @TotalCount     INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(MAX), @StartRow INT = (@PageIndex - 1) * @PageSize;

    -- 總筆數
    SET @Sql = 'SELECT @TotalCount = COUNT(*) FROM ' + @TableName + 
               CASE WHEN @WhereClause <> '' THEN ' WHERE ' + @WhereClause ELSE '' END;
    EXEC sp_executesql @Sql, N'@TotalCount INT OUTPUT', @TotalCount OUTPUT;

    -- 分頁查詢
    SET @Sql = 'WITH t AS (SELECT ' + @Fields + ', ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS rn ' +
               'FROM ' + @TableName + 
               CASE WHEN @WhereClause <> '' THEN ' WHERE ' + @WhereClause ELSE '' END + ') ' +
               'SELECT ' + @Fields + ' FROM t WHERE rn BETWEEN ' + CAST(@StartRow + 1 AS VARCHAR) + 
               ' AND ' + CAST(@StartRow + @PageSize AS VARCHAR);
    EXEC sp_executesql @Sql;
END

效能對比與建議

指標	NTILE	ROW_NUMBER
掃描次數	2 次 (COUNT + NTILE)	1 次
每頁筆數穩定	否 (末頁可能較少)	是
排序彈性	同 ORDER BY	同 ORDER BY
適用場景	粗略分桶、統計分布	精確分頁、生產環境首選

**生產建議**：統一使用 ROW_NUMBER()，配合 OFFSET/FETCH (SQL 2012+) 更簡潔：

SELECT * FROM testtable
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;