SQL Server下7种“数据分页”方案全网最新最全

SQL Server下7种“数据分页”方案全网最新最全攻略

数据分页是应用程序中十分重要的功能之一，它可以提高数据处理效率，减轻服务器负担，从而保证用户体验。SQL Server提供了多种数据分页方案，本文将描述其中7种最常用的方案，以及它们的使用场景和优缺点。

常规分页

实现方式

常规分页是最简单常用的分页方式，其实现方式为：

SELECT *
FROM  (SELECT ROW_NUMBER() OVER (ORDER BY id DESC) AS RowNumber, *
       FROM   MyTable) AS TempTable
WHERE  RowNumber BETWEEN 1 AND 10

其中，ROW_NUMBER() OVER (ORDER BY id DESC)用于对数据集进行排序并为每一行添加一个数值，用来记录行号。TempTable是一个子查询，其结果是添加了行号的数据集。最后，在TempTable基础上进行分页操作。

使用场景

常规分页适用于数据量较小，且对分页速度没有很高要求的场景。它是一种可靠且易于实现的分页方案，可以满足大部分需求。

优缺点

常规分页的优点是实现简单，易于理解；缺点是随着数据量的增加，查询效率会逐渐降低，可能导致性能瓶颈。

基于OFFSET/FETCH的分页

实现方式

OFFSET/FETCH分页是SQL Server 2012及以上版本提供的分页方式，其实现方式为：

SELECT *
FROM   MyTable
ORDER BY id DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

其中，OFFSET子句定义了起始行号，FETCH NEXT子句定义了每页返回的行数。

使用场景

OFFSET/FETCH分页适用于数据量较大，且需要快速分页的场景。它比常规分页效率更高，能够提高数据读取速度，降低CPU占用率。

优缺点

OFFSET/FETCH分页的优点是效率高，易于维护；缺点是只能用于SQL Server 2012及以上版本，不兼容更早版本的数据库。

基于TOP和NOT IN的分页

实现方式

基于TOP和NOT IN的分页是一种比较特殊的分页方式，其实现方式为：

SELECT TOP 10 *
FROM   MyTable
WHERE  id NOT IN (SELECT TOP 20 id FROM MyTable ORDER BY id DESC )
ORDER BY id DESC

其中，第一条SELECT语句用于返回指定数量的结果，第二条SELECT语句用于排除前面页数的数据，ORDER BY子句用于对数据进行降序排序。

使用场景

基于TOP和NOT IN的分页适用于数据量较小，且对查询速度要求较高的场景，具有一定的容错性。

优缺点

基于TOP和NOT IN的分页的优点是效率较高，实现简单；缺点是不能适用于数据量非常大的场景，而且如果MyTable中的id列没有索引，查询效率可能较低。

基于ROW_NUMBER和子查询的分页

实现方式

基于ROW_NUMBER和子查询的分页是一种能够处理大数据量的分页方式，其实现方式为：

SELECT  *
FROM    ( SELECT ROW_NUMBER() OVER ( ORDER BY id DESC ) AS RowNum, *
           FROM    MyTable
         ) AS TempTable
WHERE   RowNum BETWEEN 1 AND 10
ORDER BY id DESC

其中，ROW_NUMBER函数用于为数据集中的每条记录添加行号，子查询用于处理行号分页后的结果集。最后，在此结果集上再次进行排序。

使用场景

基于ROW_NUMBER和子查询的分页适用于数据量较大的场景，能够提高查询效率。

优缺点

基于ROW_NUMBER和子查询的分页的优点是适用于处理大数据量，而且查询效率较高；缺点是代码可读性较差，易产生歧义。

基于FETCH与OFFSET的OFFSET AND FETCH NEXT分页

实现方式

基于FETCH与OFFSET的OFFSET AND FETCH NEXT分页是一个完整的OFFSET FETCH SQL Server 版本，即适用于SQL Server 2012以及以上版本，其实现方式为：

SELECT *
FROM   MyTable
ORDER BY id DESC
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

在 OFFSET FETCH 中的关键字是FETCH 和 OFFSET，在该查询的情况下，将榜单中的前10个列表显示给用户浏览。

使用场景

基于FETCH与OFFSET的OFFSET AND FETCH NEXT分页适用于任何浏览你的榜单的用户，简单易用

优缺点

基于FETCH与OFFSET的OFFSET AND FETCH NEXT分页的优点是它是最好的分页方法，因为它通过合并 FETCH 和 OFFSET 语句来提高访问飞快的速度，而且其代码风格既简单易懂，又将数据分组呈现，可自如快速掌握；缺点则是只适用于SQL Server 2012及以上版本。

基于递归CTE分页

实现方式

递归CTE分页是一种比较复杂的分页方式，其实现方式为：

WITH PagingCTE AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY id DESC) AS RowNum
  FROM MyTable
),
CTE AS
(
  SELECT *, COUNT(*) OVER() AS TotalRows
  FROM PagingCTE
  WHERE RowNum BETWEEN 1 AND 10
  UNION ALL
  SELECT *, TotalRows
  FROM CTE
  WHERE RowNum BETWEEN 1 AND 10
)
SELECT *
FROM CTE
WHERE RowNum BETWEEN 1 AND 10
ORDER BY id DESC
OPTION (MAXRECURSION 0)

其中，分页查询首先用一条普通的SELECT语句将要分页的数据集包裹起来并添加行号，接着使用CTE（公共表表达式）将分页后的数据，再经过联合 WHERE 子句过滤器进行递归返回结果集。

使用场景

递归CTE分页适用于需要对比多层数据进行分页操作的场景，具有较高的灵活性。

优缺点

递归CTE分页的优点是对于任意的分页数据集都适用，具有较高的灵活性；缺点是实现相对复杂，执行效率较低。

基于动态分页的存储过程

实现方式

基于动态分页的存储过程是一种将分页处理逻辑封装在存储过程中的方式，具有较高的可维护性，其实现方式为：

CREATE PROCEDURE MyPagination
   @PageNumber INT,
   @PageSize INT
AS 
BEGIN 
    DECLARE @Skip INT, @Top INT 
    SET @Skip = (@PageNumber - 1) * @PageSize 
    SET @Top = @PageSize 

    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT *
        FROM 
            (SELECT ROW_NUMBER() OVER (ORDER BY id DESC) AS RowNumber, *
             FROM MyTable) AS TempTable
         WHERE TempTable.RowNumber BETWEEN ' + CAST(@Skip AS NVARCHAR(MAX)) + ' AND ' + CAST(@Skip + @Top AS NVARCHAR(MAX))

    EXEC(@SQL)
END

在存储过程中，通过定义输入参数PageNumber和PageSize来指定当前页数和每页显示数据的数量，使用DECLARE语句定义了Skip和Top变量，使用一条包含变量的SELECT语句来查询适合当前分页条件的数据。

使用场景

基于动态分页的存储过程适用于需要动态调整分页显示的需求，具有较高的可维护性和扩展性。

优缺点

基于动态分页的存储过程的优点是维护和修改容易，代码更加简洁明了；缺点是存储过程使用复杂，可能会增加数据库负载。

结论

SQL Server提供了多种数据分页方案，不同的分页方案适用于不同的数据量和查询需求。在实际开发中，应该根据数据量大小和查询效率要求来选择合适的分页方案。例如，常规分页方式可以实现简单的分页显示，但当数据量变大时，效率将会受到一定的影响。而基于OFFSET/FETCH的分页由于不需要进行子查询，所以相对执行速度更快，其使用起来也更加灵活。

综上所述，在SQL Server中，选择合适的数据分页方案，可以提高数据处理效率，减轻服务器负担，从而保证用户体验。