如何编写SQL Server存储过程实现通用分页查询？

SQL Server 2005引入的ROW_NUMBER()函数为数据库分页查询提供了原生支持，配合公用表表达式（CTE）可以优雅地实现按页返回数据。相比早期使用临时表、自增列或TOP嵌套的做法，基于ROW_NUMBER()的分页存储过程逻辑更清晰，也能更好地与查询优化器配合。本文将围绕一个可直接运行的存储过程展开，说明参数设计、排序字段选择、动态表名安全处理以及深分页优化等关键问题。

一、分页存储过程要解决的核心问题

数据库分页并不是简单地把结果集切一下。一个可靠的分页存储过程至少需要同时返回当前页数据与总记录数，这样前端才能正确渲染分页控件。总记录数的统计可以使用COUNT(*)，但如果表数据量很大，每次翻页都执行一次全表扫描显然不可接受。因此存储过程内部通常会单独维护一条计数查询，并尽量让计数查询命中索引或使用WITH (NOLOCK)提示降低锁开销。

分页查询还会遇到排序稳定性的问题。假设只用OrderDate排序，而同一个日期下存在大量记录，那么两次翻页之间可能因为执行计划变化或并发写入导致同一条记录出现在不同页，或者某条记录被重复读取。解决方案是在排序键中追加唯一列，通常是主键，例如ORDER BY OrderDate DESC, OrderID DESC，这样可以保证排序结果确定且分页过程不重不漏。

对于需要支持多种表结构的分页场景，存储过程还需要处理动态表名、动态列名和动态查询条件。直接拼接字符串很容易产生SQL注入风险，因此必须使用QUOTENAME对数据库对象名进行包装，同时条件部分采用参数化查询交给sp_executesql执行。下文给出的通用版本会重点展示这部分安全处理。

二、基于ROW_NUMBER的通用分页存储过程实现

固定表结构的分页存储过程思路最直接：先用COUNT(*)统计总数并输出，再将原始查询包裹在CTE中，通过ROW_NUMBER()生成连续行号，最后根据起始行号和结束行号过滤出当前页数据。下面以订单表Orders为例，假设主键为OrderID，并按OrderID倒序分页。

CREATE PROCEDURE dbo.usp_PagedOrders
    @PageIndex INT = 1,
    @PageSize INT = 20,
    @TotalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @StartRow INT;
    DECLARE @EndRow INT;
    SET @StartRow = (@PageIndex - 1) * @PageSize + 1;
    SET @EndRow = @StartRow + @PageSize - 1;

    SELECT @TotalCount = COUNT(*)
    FROM dbo.Orders WITH (NOLOCK);

    ;WITH OrderData AS
    (
        SELECT
            OrderID,
            CustomerID,
            OrderDate,
            TotalAmount,
            ROW_NUMBER() OVER (ORDER BY OrderID DESC) AS RowNum
        FROM dbo.Orders WITH (NOLOCK)
    )
    SELECT
        OrderID,
        CustomerID,
        OrderDate,
        TotalAmount
    FROM OrderData
    WHERE RowNum BETWEEN @StartRow AND @EndRow
    ORDER BY RowNum;
END
GO

这段代码的逻辑非常清晰：ROW_NUMBER()按照OrderID DESC为每一行分配一个从1开始的连续编号，CTE只负责编号，外层查询通过BETWEEN截取需要的区间。页码@PageIndex从1开始计算，起始行号等于(@PageIndex - 1) * @PageSize + 1，结束行号由@StartRow + @PageSize - 1得到。由于行号是连续的，BETWEEN可以准确取出第N页的所有行，不会漏也不会重复。

如果系统中存在多张表都需要分页，为每张表都写一个固定存储过程并不现实。此时可以设计一个通用版本，把表名、列名、排序列和查询条件作为参数传入。动态SQL的风险在于对象名不能直接参数化，必须使用QUOTENAME将表名和列名用方括号包裹，而查询条件则通过sp_executesql的参数集合进行绑定，从根本上阻断注入。

CREATE PROCEDURE dbo.usp_GenericPaging
    @TableName NVARCHAR(128),
    @ColumnList NVARCHAR(MAX),
    @OrderColumn NVARCHAR(128),
    @PageIndex INT = 1,
    @PageSize INT = 20,
    @WhereClause NVARCHAR(MAX) = '',
    @TotalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @StartRow INT;
    DECLARE @EndRow INT;
    SET @StartRow = (@PageIndex - 1) * @PageSize + 1;
    SET @EndRow = @StartRow + @PageSize - 1;

    DECLARE @CountSql NVARCHAR(MAX);
    SET @CountSql = N'SELECT @TotalCount = COUNT(*) FROM ' + QUOTENAME(@TableName) +
                    CASE WHEN @WhereClause <> '' THEN N' WHERE ' + @WhereClause ELSE N'' END;

    EXEC sp_executesql @CountSql, N'@TotalCount INT OUTPUT', @TotalCount OUTPUT;

    DECLARE @Sql NVARCHAR(MAX);
    SET @Sql = N';WITH PagedData AS
    (
        SELECT ' + @ColumnList + N',
               ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderColumn) + N') AS RowNum
        FROM ' + QUOTENAME(@TableName) +
        CASE WHEN @WhereClause <> '' THEN N' WHERE ' + @WhereClause ELSE N'' END + N'
    )
    SELECT ' + @ColumnList + N'
    FROM PagedData
    WHERE RowNum BETWEEN @StartRow AND @EndRow
    ORDER BY RowNum;';

    EXEC sp_executesql @Sql, N'@StartRow INT, @EndRow INT', @StartRow, @EndRow;
END
GO

通用版本将计数查询和分页查询都交给sp_executesql执行。表名经过QUOTENAME包装后，即使传入类似Orders; DROP TABLE Orders的恶意字符串，也只会被当作一个完整的对象名处理，无法破坏SQL语句结构。@WhereClause用于传递额外的查询条件，调用方仍然可以使用参数化形式，但存储过程本身无法自动推断条件中的参数类型，因此实际项目中通常会把@WhereClause设计成由调用方拼接好并自行负责参数绑定，或者改用专门的筛选参数。

三、深分页性能优化与键集分页方案

基于ROW_NUMBER()的分页在数据量较小时表现优秀，但当页码非常靠后时，性能会明显下降。原因在于ROW_NUMBER()必须为结果集排序并生成全部行号，然后才能过滤出第N页。例如查询第500页时，如果每页20条，数据库实际上需要扫描并排序前10000行，虽然只返回20行，但排序和行号计算成本已经发生。这种深分页问题并非SQL Server独有，在MySQL的LIMIT和PostgreSQL的OFFSET中也同样存在。

优化深分页的第一种方式是确保排序列上有覆盖索引，并且索引顺序与ORDER BY方向一致。例如按OrderID DESC分页时，如果主键就是OrderID且为聚集索引，那么排序阶段可以直接利用索引顺序，不必额外执行排序操作。对于包含OrderDate的排序，可以创建OrderDate DESC, OrderID DESC的复合索引，并让查询只返回索引覆盖的列，从而减少键查找和排序开销。

更彻底的优化方案是使用键集分页，也称为本页锚点分页。它的核心思路是记住上一页最后一条记录的主键或排序键值，下一页直接从这个键值之后开始取TOP，而不是依靠行号偏移。键集分页避免了生成大量行号，无论翻到第几页，执行计划都只扫描需要返回的那一批数据，因此深分页性能几乎恒定。下面以OrderID作为锚点列给出示例。

CREATE PROCEDURE dbo.usp_KeysetPagingOrders
    @LastOrderID INT = NULL,
    @PageSize INT = 20
AS
BEGIN
    SET NOCOUNT ON;

    IF @LastOrderID IS NULL
        SELECT TOP (@PageSize)
               OrderID,
               CustomerID,
               OrderDate,
               TotalAmount
        FROM dbo.Orders WITH (NOLOCK)
        ORDER BY OrderID DESC;
    ELSE
        SELECT TOP (@PageSize)
               OrderID,
               CustomerID,
               OrderDate,
               TotalAmount
        FROM dbo.Orders WITH (NOLOCK)
        WHERE OrderID < @LastOrderID
        ORDER BY OrderID DESC;
END
GO

键集分页要求排序键必须是唯一且可比较的，通常使用自增主键。调用方需要保存当前页最后一条记录的OrderID，请求下一页时将其传入@LastOrderID。如果用户跳页而不是顺序翻页，键集分页无法像行号分页那样直接跳到指定页码，因此两种方案可以结合使用：普通翻页用键集分页，跳页场景退回ROW_NUMBER()分页并接受一定性能损失。

SQL Server 2012及更高版本还提供了OFFSET ... FETCH NEXT语法，它本质上仍属于偏移分页，深分页性能与ROW_NUMBER()类似，但语法更简洁。在SQL Server 2005环境中无法使用OFFSET FETCH，因此本文重点介绍ROW_NUMBER()方案。无论采用哪种分页方式，索引设计和排序唯一性都是决定性能与正确性的关键因素。

四、执行示例与常见问题排查

存储过程部署完成后，可以通过一段简单的T-SQL脚本验证分页效果。下面调用固定表版本，请求第2页数据并获取总记录数。

DECLARE @Total INT;
EXEC dbo.usp_PagedOrders
    @PageIndex = 2,
    @PageSize = 20,
    @TotalCount = @Total OUTPUT;

SELECT @Total AS TotalRecords;

执行后结果集会返回当前页的20行记录以及总记录数。如果总行数不足40条，第2页返回的行数会少于20条；如果@PageIndex超出实际页码范围，结果集为空但总记录数仍然正确。调用方可以根据@TotalCount与@PageSize计算总页数，并对页码做边界保护。

实际使用中经常遇到排序字段不唯一导致分页数据错乱的问题。例如按OrderDate排序，而多个订单日期相同，翻页后可能出现同一行被两次显示。解决办法是在ORDER BY中追加主键，例如ORDER BY OrderDate DESC, OrderID DESC，无论日期如何重复，最终排序结果都会由主键唯一决定。这也是为什么分页查询中的排序列通常不建议只用普通业务字段。

另一个常见问题是动态表名版本中传入的条件字段没有做好参数类型转换。sp_executesql要求参数类型与SQL文本中的引用类型严格匹配，如果条件参数是字符串却传入NVARCHAR，或者日期格式不正确，就可能导致隐式转换或索引失效。维护时建议将条件参数分类，尽量使用固定类型的专用参数，避免把所有过滤条件都揉进一个@WhereClause字符串。

最后需要关注的是事务与锁。如果分页查询发生在高频写入的表上，默认读取可能被写操作阻塞。示例中使用了WITH (NOLOCK)提示来模拟非锁定读，但该提示会带来脏读风险，不适合对数据一致性要求严格的场景。生产环境更推荐使用READ COMMITTED SNAPSHOT隔离级别，通过行版本控制避免读写相互阻塞，同时不牺牲读取的准确性。合理配置索引、排序唯一性和隔离级别后，SQL Server分页存储过程可以在百万级甚至千万级数据下保持稳定、高效的返回速度。

SQL Server分页存储过程ROW_NUMBER分页存储过程分页代码修改时间：2026-08-21 20:56:15