您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 高效能分頁查詢實戰：SET ROWCOUNT 與游標法完整解析

編輯：關於SqlServer

面對千萬列資料表，傳統 TOP + NOT IN 或 ROW_NUMBER() 分頁效能隨頁碼遞增而急劇下降。本文詳細解析利用 SET ROWCOUNT 快速定位游標位置，配合索引鍵值篩選的高效能分頁技術，並提供完整預存程序與 C# 分頁元件實作。

核心原理

假設資料表有已建立索引的整數主鍵 ID
使用 SET ROWCOUNT N 讓查詢在讀取 N 列後停止，利用索引順序快速「跳過」前 N 列
記錄第 N 列的 ID 值，再以 WHERE ID >= @ID + SET ROWCOUNT PageSize 取得目標頁資料
索引範圍掃描 (Range Scan) 替代全表掃描，效能與頁碼無關

基本範例 (正序分頁)

DECLARE @ID INT, @MoveRecords INT;
SET @MoveRecords = @CurrentPage * @PageSize + 1;

SET ROWCOUNT @MoveRecords;
SELECT @ID = ID FROM Table1 ORDER BY ID;  -- 快速定位到目標頁起始列

SET ROWCOUNT @PageSize;
SELECT * FROM Table1 WHERE ID >= @ID ORDER BY ID;  -- 取得該頁資料

SET ROWCOUNT 0;  -- 重置

倒序分頁修改

-- 定位
SET ROWCOUNT @MoveRecords;
SELECT @ID = ID FROM Table1 ORDER BY ID DESC;

-- 取資料
SET ROWCOUNT @PageSize;
SELECT * FROM Table1 WHERE ID <= @ID ORDER BY ID DESC;

SET ROWCOUNT 0;

完整預存程序：Tag_Page_Name_Select

CREATE PROCEDURE [dbo].[Tag_Page_Name_Select]
    @MaxPageSize INT,           -- 每頁筆數
    @PageNum INT,               -- 當前頁碼 (1-based)
    @Count INT OUTPUT           -- 總筆數輸出
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Name NVARCHAR(50), @Cursor INT;

    -- 1. 取得總筆數
    SELECT @Count = COUNT(tag_Name) FROM [viewdatabase0716].[dbo].[view_tag];

    -- 2. 計算游標起始位置
    SET @Cursor = @MaxPageSize * (@PageNum - 1) + 1;

    -- 3. 邊界修正：頁碼超過總頁數時調整至最後一頁
    IF @Cursor > @Count
    BEGIN
        IF @Count % @MaxPageSize = 0
            SET @Cursor = CASE WHEN @Cursor > @MaxPageSize THEN @Count - @MaxPageSize + 1 ELSE 1 END;
        ELSE
            SET @Cursor = @Count - (@Count % @MaxPageSize) + 1;
    END

    -- 4. 快速定位到起始列
    SET ROWCOUNT @Cursor;
    SELECT @Name = tag_Name FROM [viewdatabase0716].[dbo].[view_tag] ORDER BY tag_Name;

    -- 5. 取得該頁資料
    SET ROWCOUNT @MaxPageSize;
    SELECT * FROM [viewdatabase0716].[dbo].[view_tag]
    WHERE tag_Name >= @Name ORDER BY tag_Name;

    SET ROWCOUNT 0;
END

C# 分頁元件：ExStringBuilder

封裝分頁邏輯與 HTML 生成，支援：

可設定頁碼 (PageNum)、頁大小 (MaxPageSize)、顯示頁碼數 (MaxPages)
自動計算總頁數 (AllPage)、邊界修正
智慧頁碼範圍計算 (當前頁置中、首尾頁碼恆顯示)
產生上一頁/下一頁、首頁/末頁、頁碼列表 HTML

public class ExStringBuilder {
    private StringBuilder InsertString, PageString;
    private int PrivatePageNum = 1, PrivateMaxPageSize = 25, PrivateMaxPages = 10;
    private int PrivateCount, PrivateAllPage;

    // 屬性：PageNum, MaxPageSize, MaxPages, DateCount, AllPage, GetHtml, GetPageHtml

    public void Pagination() {
        PageString = new StringBuilder();
        PrivateAllPage = (int)Math.Ceiling((decimal)PrivateCount / PrivateMaxPageSize);
        if (PrivatePageNum > PrivateAllPage) PrivatePageNum = PrivateAllPage;

        int LeftRange = (PrivateMaxPages + 1) / 2 - 1;
        int RightRange = (PrivateMaxPages + 1) / 2;
        int LeftStart, RightEnd;

        if (PrivateMaxPages >= PrivateAllPage) { LeftStart = 1; RightEnd = PrivateAllPage; }
        else if (PrivatePageNum <= LeftRange) { LeftStart = 1; RightEnd = LeftStart + PrivateMaxPages - 1; }
        else if (PrivateAllPage - PrivatePageNum < RightRange) { RightEnd = PrivateAllPage; LeftStart = RightEnd - PrivateMaxPages + 1; }
        else { LeftStart = PrivatePageNum - LeftRange; RightEnd = PrivatePageNum + RightRange; }

        // ... 生成 HTML 邏輯 (省略，詳見原文)
    }

    public void TagTable(ExDataRow row) { /* 生成資料列 HTML */ }
}

前端呼叫範例

// 1. 取得分頁參數 (從 Session)
ExRequest myExRequest = new ExRequest();
myExRequest.PageSession("Tag_", new string[] { "page", "size" });

// 2. 設定分頁元件
ExStringBuilder Tag = new ExStringBuilder();
Tag.MaxPageSize = Convert.ToInt32(Session["Tag_size"]);
Tag.MaxPages = 9;
Tag.PageNum = Convert.ToInt32(Session["Tag_page"]);

// 3. 呼叫預存程序
string[][] param = {
    new string[] { "MaxPageSize", "PageNum", "Count" },
    new string[] { Tag.MaxPageSize.ToString(), Tag.PageNum.ToString() }
};
DataTable dt = MySQL.BatchGetDB("Tag_Page_Name_Select", param, "Count");
Tag.DateCount = (int)MySQL.OutputCommand.Parameters["@Count"].Value;
Tag.Pagination();

// 4. 輸出
HeadPage.InnerHtml = FootPage.InnerHtml = Tag.GetPageHtml;
foreach (DataRow row in dt.Rows) Tag.TagTable(new ExDataRow(row));
TagBox.InnerHtml = Tag.GetHtml;

效能測試結果

千萬列資料表，直接跳至第 100 頁 (每頁 100 列)：**毫秒級回應**，無隨頁碼遞增的效能衰減。

注意事項與現代替代方案

SET ROWCOUNT 在 SQL Server 2012+ 仍支援，但官方建議改用 OFFSET ... FETCH NEXT (ANSI 標準)
OFFSET/FETCH 同樣支援索引求值，語法更清晰：
ORDER BY ID OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY
若主鍵非連續或有刪除間隙，本法仍正確 (以實際資料列數定位，非 ID 值)
複雜排序/多欄位排序需建立對應覆蓋索引