高效能分頁存儲過程：SET ROWCOUNT + 臨時表索引鍵回查

核心技巧

1. SET ROWCOUNT @PageUpperBound：限制後續查詢最多回傳 N 筆，達到上限立即停止掃描。
2. 先將主鍵 id 插入暫存表 @indextable，僅掃描索引，極窄、極快。
3. 再以暫存表主鍵 JOIN 回主表只取需要的頁面範圍，避免全表掃描與大量書籤查詢。

通用分頁存儲過程範本

CREATE PROCEDURE Paging_RowCount
    @Tables      VARCHAR(1000),   -- 可含 JOIN
    @PK          VARCHAR(100),    -- 主鍵欄位
    @Sort        VARCHAR(200) = NULL,  -- 排序欄位，可含 ASC/DESC
    @PageNumber  INT = 1,
    @PageSize    INT = 10,
    @Fields      VARCHAR(1000) = '*',
    @Filter      VARCHAR(1000) = NULL, -- WHERE 條件
    @Group       VARCHAR(1000) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. 預設排序
    IF @Sort IS NULL OR @Sort = '' SET @Sort = @PK;
    
    -- 2. 解析排序方向與欄位
    DECLARE @Operator CHAR(2), @SortCol VARCHAR(200);
    IF CHARINDEX('DESC', @Sort) > 0 BEGIN
        SET @SortCol = REPLACE(@Sort, 'DESC', '');
        SET @Operator = '<=';
    END ELSE BEGIN
        SET @SortCol = REPLACE(@Sort, 'ASC', '');
        SET @Operator = '>=';
    END
    
    -- 3. 計算分頁邊界
    DECLARE @PageLowerBound INT = (@PageNumber - 1) * @PageSize;
    DECLARE @PageUpperBound INT = @PageNumber * @PageSize;
    
    -- 4. 取主鍵進暫存表（SET ROWCOUNT 截斷掃描）
    DECLARE @IndexTable TABLE (nid INT IDENTITY(1,1), pk_val SQL_VARIANT);
    SET ROWCOUNT @PageUpperBound;
    INSERT INTO @IndexTable (pk_val)
    EXEC('SELECT ' + @PK + ' FROM ' + @Tables + 
         CASE WHEN @Filter IS NOT NULL THEN ' WHERE ' + @Filter ELSE '' END +
         CASE WHEN @Group IS NOT NULL THEN ' GROUP BY ' + @Group ELSE '' END +
         ' ORDER BY ' + @SortCol + ' ' + CASE WHEN @Operator = '<=' THEN 'DESC' ELSE 'ASC' END);
    SET ROWCOUNT 0;
    
    -- 5. 取總筆數（可選）
    -- SELECT COUNT(*) FROM @IndexTable; -- 實際總筆數可能大於 @PageUpperBound，需另查
    
    -- 6. 回主表取明細
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = 'SELECT ' + @Fields + ' FROM ' + @Tables + ' t ' +
               'INNER JOIN @IndexTable i ON t.' + @PK + ' = i.pk_val ' +
               'WHERE i.nid > ' + CAST(@PageLowerBound AS VARCHAR) +
               ' ORDER BY i.nid';
    EXEC sp_executesql @SQL;
END

效能優勢對比

注意事項

• SET ROWCOUNT 不影響 INSERT/UPDATE/DELETE 的 TOP 子句（SQL 2005+ 建議改用 TOP）。
• 動態 SQL 需防注入：@Tables、@Filter 應在應用層驗證白名單。
• 大表建議配合覆蓋索引 INDEX (sort_col) INCLUDE (pk, fields...) 讓步驟 4 純索引掃描。