sqlserver存储过程分页查询

在SQL Server中，分页是一种常见的需求，用于从大量数据中提取一部分数据进行显示或处理，分页存储过程是一种常用的实现分页的方法，它可以避免在客户端进行大量的数据处理，提高应用程序的性能，本文将介绍五种SQL Server分页存储过程的方法及性能比较。

1、使用ROW_NUMBER()函数

ROW_NUMBER()函数是SQL Server中的一种窗口函数，它可以为每一行分配一个唯一的序号，通过使用ROW_NUMBER()函数，我们可以很容易地实现分页功能，以下是一个简单的示例：

CREATE PROCEDURE PageByRowNumber
    @TableName NVARCHAR(128),
    @OrderColumn NVARCHAR(128),
    @PageSize INT,
    @PageIndex INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderColumn) + N') AS RowNum FROM ' + QUOTENAME(@TableName) + N') AS T WHERE T.RowNum > ' + CAST((@PageIndex 1) * @PageSize + 1 AS NVARCHAR) + N' AND T.RowNum <= ' + CAST(@PageIndex * @PageSize AS NVARCHAR);
    EXEC sp_executesql @sql;
END;

2、使用OFFSET FETCH子句

OFFSET FETCH子句是SQL Server 2012引入的一个新特性，它允许我们直接在查询中实现分页，以下是一个简单的示例：

CREATE PROCEDURE PageByOffsetFetch
    @TableName NVARCHAR(128),
    @OrderColumn NVARCHAR(128),
    @PageSize INT,
    @PageIndex INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' ORDER BY ' + QUOTENAME(@OrderColumn) + N' OFFSET ' + CAST((@PageIndex 1) * @PageSize AS NVARCHAR) + N' ROWS FETCH NEXT ' + CAST(@PageSize AS NVARCHAR) + N' ROWS ONLY';
    EXEC sp_executesql @sql;
END;

3、使用ROW_NUMBER()和CTE（公共表表达式）结合

我们可以将ROW_NUMBER()函数与CTE结合使用，以实现更复杂的分页逻辑，以下是一个简单的示例：

WITH OrderedData AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderColumn) + N') AS RowNum FROM ' + QUOTENAME(@TableName) + N'
)
SELECT * FROM OrderedData WHERE RowNum > ' + CAST((@PageIndex 1) * @PageSize + 1 AS NVARCHAR) + N' AND RowNum <= ' + CAST(@PageIndex * @PageSize AS NVARCHAR);

4、使用ROW_NUMBER()和JOIN结合

我们还可以将ROW_NUMBER()函数与JOIN操作结合使用，以实现更复杂的分页逻辑，以下是一个简单的示例：

WITH OrderedData AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderColumn) + N') AS RowNum FROM ' + QUOTENAME(@TableName) + N''
)
SELECT DISTINCT * FROM OrderedData t1 INNER JOIN OrderedData t2 ON t1.RowNum = t2.RowNum 1 OR t1.RowNum = t2.RowNum + 1;

5、使用TOP和UNION ALL结合

我们可以使用TOP和UNION ALL结合实现分页功能，以下是一个简单的示例：

WITH OrderedData AS (
    SELECT TOP (@PageSize) * FROM ' + QUOTENAME(@TableName) + N' ORDER BY ' + QUOTENAME(@OrderColumn) + N' UNION ALL
    SELECT TOP (@PageSize) * FROM ' + QUOTENAME(@TableName) + N' ORDER BY ' + QUOTENAME(@OrderColumn) + N' DESC' -如果需要倒序分页，可以添加这一行代码，并调整前面的TOP语句中的排序方向为ASC
)
SELECT * FROM OrderedData;

性能比较：

1、使用ROW_NUMBER()函数的方法具有较高的灵活性，可以实现各种复杂的分页逻辑，这种方法的性能可能不如其他方法，因为它需要在查询中执行窗口函数，如果表中的数据量很大，这种方法可能会导致性能下降。

2、使用OFFSET FETCH子句的方法在SQL Server 2012及更高版本中具有较好的性能，这种方法可以直接在查询中实现分页，无需在服务器端执行额外的操作，这种方法的兼容性较差，只能在SQL Server 2012及更高版本中使用。

3、使用ROW_NUMBER()和CTE结合的方法具有较高的灵活性，可以实现各种复杂的分页逻辑，这种方法的性能可能不如其他方法，因为它需要在查询中执行窗口函数和CTE操作，如果表中的数据量很大，这种方法可能会导致性能下降。