SQL Server 2005 ROW_NUMBER() 實作高效分頁查詢

SQL Server 2005 引入 ROW_NUMBER() OVER() 視窗函數，讓分頁查詢變得簡單高效。本文提供標準分頁範本與一個通用分頁預存程式碼（支援動態資料表、欄位、排序、條件），並說明首頁優化邏輯。

標準分頁範本

DECLARE @pagenum INT = 2, @pagesize INT = 3;
SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY newsid DESC) AS rownum,
           newsid, topic, ntime, hits
    FROM news
) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC;

ASP.NET 僅需傳入頁碼與頁大小參數。

通用分頁預存程式

CREATE PROCEDURE news_Showlist
    @tblName VARCHAR(255), @strGetFields VARCHAR(1000), @fldName VARCHAR(255),
    @PageSize INT, @PageIndex INT, @strWhere VARCHAR(1500), @Sort VARCHAR(255)
AS
BEGIN
    DECLARE @strSQL VARCHAR(5000), @strTmp VARCHAR(110), @strOrder VARCHAR(400);
    IF @Sort = 'desc' BEGIN SET @strTmp = '<(select min'; SET @strOrder = ' ORDER BY ' + @fldName + ' DESC'; END
    ELSE BEGIN SET @strTmp = '>(select max'; SET @strOrder = ' ORDER BY ' + @fldName + ' ASC'; END
    IF @PageIndex = 1 BEGIN
        SET @strSQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @strGetFields + ' FROM ' + @tblName +
                      CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END + @strOrder;
    END ELSE BEGIN
        SET @strSQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @strGetFields + ' FROM ' + @tblName +
                      ' WHERE ' + @fldName + @strTmp + '(' + @fldName + ') FROM ' +
                      '(SELECT TOP ' + STR((@PageIndex-1)*@PageSize) + ' ' + @fldName + ' FROM ' + @tblName +
                      CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END + @strOrder + ') AS tblTmp)' +
                      CASE WHEN @strWhere != '' THEN ' AND ' + @strWhere ELSE '' END + @strOrder;
    END
    EXEC (@strSQL);
END

首頁（@PageIndex=1）直接用 TOP 查詢，避免子查詢開銷，提升效能。