MSSQL高效分页存储过程实现方案

结果与目标

在几百万条数据的实际测试中，许多通用分页插件或简单的 TOP ... NOT IN 写法会导致查询耗时剧增。本方案的目标是实现：

• 高扩展性：兼容任意表结构、字段组合及复杂WHERE条件。
• 高性能：利用索引友好的排序机制，避免全表扫描导致的超时。
• 完整性：单次调用同时返回当前页数据和总页数计算所需的总记录数。

准备条件

在执行以下操作前，请确保具备以下条件：

1. 数据库版本：SQL Server 2005及以上版本（必须支持 ROW_NUMBER() 窗口函数）。
2. 索引准备：建议对作为排序依据的字段（特别是主键或唯一索引列）建立聚集索引或非聚集索引，否则性能优势无法体现。
3. 权限配置：执行存储过程的账户需具有相关表的查询权限及动态SQL执行权限。

真实操作顺序

第1步：创建分页存储过程

目的：封装分页逻辑，将易变的查询参数与固定的SQL结构分离，减少网络传输并提升复用性。

入口：SQL Server Management Studio (SSMS) 查询窗口或应用程序初始化脚本。

动作：复制并执行以下代码，系统将创建名为 P_GridViewPager 的存储过程。

CREATE PROCEDURE [dbo].[P_GridViewPager] (
    @recordTotal INT OUTPUT,      -- 输出记录总数
    @viewName VARCHAR(800),       -- 表名或视图名
    @fieldName VARCHAR(800) = '*',-- 查询字段列表，默认为所有字段
    @keyName VARCHAR(200) = 'Id', -- 主键或排序关键索引字段
    @pageSize INT = 20,           -- 每页记录数
    @pageNo INT =1,               -- 当前页码
    @orderString VARCHAR(200),    -- 排序条件，如 'CreateTime DESC'
    @whereString VARCHAR(800) = '1=1' -- WHERE 过滤条件，默认恒真
)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. 计算分页边界行号
    DECLARE @beginRow INT;
    DECLARE @endRow INT;
    SET @beginRow = (@pageNo - 1) * @pageSize + 1;
    SET @endRow = @pageNo * @pageSize;

    -- 2. 获取总记录数
    -- 注意：此处统计的是经过WHERE过滤后的数据量，而非整个表的大小
    DECLARE @tempCount NVARCHAR(1000);
    SET @tempCount = 'SELECT @recordTotal = COUNT(*) FROM (SELECT '+@keyName+' FROM '+@viewName+' WHERE '+@whereString+') AS my_temp';
    EXECUTE sp_executesql @tempCount, N'@recordTotal INT OUTPUT', @recordTotal OUTPUT;

    -- 3. 执行主查询返回当前页数据
    DECLARE @tempMain VARCHAR(4000); -- 适当增加长度以防超长SQL被截断
    SET @tempMain = 'SELECT * FROM ('
        +'SELECT ROW_NUMBER() OVER (order by '+@orderString+') AS rows,'
        +@fieldName
        +' FROM '
        +@viewName
        +' WHERE '
        +@whereString
        +') AS main_temp'
        +' WHERE rows BETWEEN '+ CAST(@beginRow AS VARCHAR) + ' AND ' + CAST(@endRow AS VARCHAR);

    EXECUTE (@tempMain);
    SET NOCOUNT OFF;
END

第2步：调用存储过程进行查询

目的：验证功能并获取数据。

入口：业务逻辑层或直接通过SQL客户端调试。

动作：声明变量接收总记录数，并传入具体参数。假设要查询 Users 表，按注册时间倒序排列，每页10条，查第1页：

DECLARE @TotalCount INT;
EXEC P_GridViewPager 
    @recordTotal = @TotalCount OUTPUT,
    @viewName = 'Users',
    @fieldName = 'UserId, UserName, Email, RegisterTime',
    @keyName = 'UserId',
    @pageSize = 10,
    @pageNo = 1,
    @orderString = 'RegisterTime DESC',
    @whereString = 'IsDeleted = 0';

SELECT @TotalCount AS TotalRecords;

完成判断

• 无报错：存储过程创建成功且调用未引发语法错误（Msg 208等）。
• 数据准确：返回的结果集行数等于 @pageSize（末页除外），且 @TotalCount 与手动执行 SELECT COUNT(*) FROM Users WHERE IsDeleted = 0 的结果一致。
• 性能达标：在百万级数据表中，单次分页查询响应时间应控制在毫秒至秒级（取决于索引质量），远快于非分页的全表加载。

简要总结

该存储过程核心在于利用 ROW_NUMBER() 生成连续行号，并通过动态SQL拼接WHERE和ORDER BY子句，实现了高度定制化的分页能力。相比SQL Server 2012引入的 OFFSET FETCH，此方法在早期版本中依然有效，且在处理复杂关联查询时灵活性更高。

以上就是MSSQL高效分页存储过程的详细内容，更多关于SQL Server数据库优化的资料请关注本站其它相关文章！