SQL Server数据分页：7种方案详解与选型建议

一、 数据库内部分页的基础逻辑

数据分页通常有三种执行层面的方案：

1. 应用层分页：将全量数据拉取至内存（如PHP、Java、C#），由代码逻辑切片。优点是逻辑简单，缺点是网络传输与内存消耗大，速度最慢。
2. 数据库层分页：利用SQL语句限制返回行数，数据库仅处理所需片段。这是本文重点讨论的方案，速度与简易性平衡较好。
3. 缓存/第三方工具分页：数据预先存入Redis等中间件，应用层对缓存切片。速度极快但架构复杂度高。

下文将深入探讨第二种方案在SQL Server中的具体实现，不同版本的数据库因内置函数差异，写法亦有所不同。

二、 SQL Server 2012 以下的经典方案

在早期版本中，由于缺乏原生的偏移量关键字，需要借助窗口函数或嵌套查询来实现分页。

第1步：使用 ROW_NUMBER() OVER() 生成行号

这是SQL Server 2005起支持的最通用且高效的分页方式，通过为每一行分配序号进行范围筛选。

-- 示例：查询第1至10条数据
SELECT * FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY menuId) AS RowId, * FROM sys_menu) AS r
WHERE RowId BETWEEN 1 AND 10

通用公式：将参数转换为变量后，BETWEEN的范围计算如下：

-- pageIndex: 当前页码 (从1开始)
-- pageSize: 每页显示数量
SELECT * FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY 排序字段) AS RowId, * FROM 表名) AS r
WHERE RowId BETWEEN ((pageIndex - 1) * pageSize + 1) AND (pageIndex * pageSize)

注意：OVER括号内的ORDER BY决定了数据排序稳定性，若存在重复值需增加次级排序字段。

第2步：使用 TOP ... NOT IN（性能较差，仅作了解）

原理是先查出不包含在“前N条”ID集合中的数据，再取前N条。虽然写法直观，但在数据量较大时，NOT IN会导致扫描效率极低。

-- 查询第11-20条记录 (pageSize=10, pageIndex=2)
SELECT TOP 10 menuId, *
FROM sys_menu 
WHERE menuId NOT IN (
    SELECT TOP 10 menuId FROM sys_menu -- 排除前10条
)

通用公式：

SELECT TOP @pageSize menuId, *
FROM sys_menu 
WHERE menuId NOT IN (
    SELECT TOP ((@pageSize-1)*@pageIndex) menuId FROM sys_menu
)

第3步：嵌套 Top 与 倒序排序（不推荐）

这种方案通过三次嵌套和反转排序来获取中间页数据，SQL语句冗长且难以维护，不建议在实际项目中采用。

第4步：使用 MAX(ID) 结合 Top

先查出上一页最后一条记录的ID，再查找大于该ID的记录。该方法依赖于有递增的主键或排序ID。

-- 查询第11-20条
SELECT TOP 10 * FROM sys_menu WHERE menuId > 
    (SELECT MAX(menuId) FROM 
     (SELECT TOP 10 menuId FROM sys_menu ORDER BY menuId) AS TEMP1)

三、 SQL Server 2012 及以上的高效方案

微软在SQL Server 2012引入了标准的 OFFSET...FETCH 语法，使其成为目前最推荐的官方分页方式。

第5步：使用 OFFSET ... FETCH NEXT

该语法语义清晰，类似于Oracle或PostgreSQL的分页机制，执行计划稳定。

-- 跳过0行，获取接下来的10行 (第1页)
SELECT * FROM sys_menu 
ORDER BY menuId 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

关键约束：必须配合 ORDER BY 使用，否则报错。句式结尾的 ROWS 和 ONLY 不可遗漏。

通用用法：

-- pageIndex: 当前页码, pageSize: 每页大小
SELECT * FROM 表名 
ORDER BY 排序字段 
OFFSET ((pageIndex - 1) * pageSize) ROWS 
FETCH NEXT pageSize ROWS ONLY

四、 进阶：不依赖排序ID的终极封装方案

上述多数方案均强依赖某个唯一的排序字段或主键（如自增ID）。如果遇到多表联合查询导致结果集无唯一主键的情况，可以使用特定框架（如DeveloperSharp）提供的 PagePartition 方法进行黑盒封装。

第6步：配置数据源类

继承特定的数据层基类并注入连接字符串。

using DeveloperSharp.Structure.Model;
using DeveloperSharp.Framework.QueryEngine;

namespace Demo
{
    // 声明SQL Server数据源
    [DataSource(DatabaseType.SQLServer, "Server=localhost;Database=Test;Uid=sa;Pwd=123")]
    public class TestData : DeveloperSharp.Structure.Model.DataLayer
    {
        // 无需编写额外代码
    }
}

第7步：调用 PagePartition 进行分页

核心仅需一行代码。无论SQL语句是否包含主键，该方法均可自动处理复杂的分页逻辑。

using DeveloperSharp.Extension; // 引入扩展命名空间

class Program
{
    static void Main(string[] args)
    {
        TestData td = new TestData();
        
        // RecordSet: 基础查询SQL (建议带上Limit或Top限制最大扫描量)
        // PageSize: 页面大小
        // PageIndex: 当前页码
        var pp = td.PagePartition("select top 5000 * from t_Order where Id>10 order by Id desc", 20, 1);

        List<Product> Products = pp.Table.ToList<Product>();
        foreach (var P in Products)
        {
            Console.WriteLine(P.Name);
        }
        Console.ReadLine();
    }
}

优势：该方法在SQL Server内部完成切片操作，且不强制要求业务表中存在单一排序键，适用于极度复杂的动态视图或多表Join场景。

五、 方案总结与选型建议

最终建议：

1. 首选：如果环境允许，一律使用 OFFSET...FETCH NEXT，语法标准且优化器对其支持最好。
2. 兼容：若必须运行在2012以下旧系统，使用 ROW_NUMBER() OVER() 是唯一兼顾性能与跨版本兼容性的选择。
3. 特殊场景：当遇到无法确定排序字段的动态复杂查询时，考虑集成具备 PagePartition 能力的ORM框架以避免重写分页逻辑。

以上就是SQL Server下7种“数据分页”方案的详细内容，更多关于SQL Server数据优化的资料请关注本站其它相关文章！