sql server中千万数量级分页存储过程代码

分页是 Web 应用中常见的功能.当数据表中拥有千万条记录时，需要进行分页查询时，直接使用基础的分页查询语句可能会导致较高的性能消耗和响应延迟.因此需要使用分页存储过程来提高查询性能.

以下是 sql server 中 千万数量级分页存储过程代码的完整攻略：

1. 分页查询的数据表

该数据表中共有 10000000 条记录，将其命名为test表.其中，主键为id，需要进行分页的条件为state=1.

1. 定义存储过程

定义名为p_Page的存储过程，传入参数为 @pageIndex, @pageSize,@pageCount.

示例：

CREATE PROCEDURE [dbo].[p_Page]
   @pageIndex INT,  --页码
   @pageSize INT, --每页数据条数
   @pageCount INT OUT --总页数
AS

SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowID, * INTO #temptable FROM  test WHERE state=1

SET @pageCount = CEILING((SELECT COUNT(*) FROM #temptable) / CAST(@pageSize AS DECIMAL(16,2)))  --总页数

SELECT * FROM #temptable WHERE RowID BETWEEN ( (@pageIndex - 1) * @pageSize + 1 ) AND ( @pageIndex * @pageSize )

DROP TABLE #temptable

1. 存储过程代码解析

a. 使用 SELECT INTO #temptable 创建临时表。

此步骤将test表中state=1的所有记录按照id排序，进行分页查询的结果保存到#temptable临时表中.

b. 使用ROW_NUMBER() OVER (ORDER BY id) AS RowID将当前行转换为数字.

ROW_NUMBER()在 SQL Server 中用来为查询结果集的每一行来赋一个行号，而使用 OVER 子句总是跟着一个 ORDER BY 子句，以确定如何为分配行号。本例中根据id进行排序.

c. 根据每页数据条数计算总页数。

在计算总页数时需要使用 CEILING() 函数和 CAST( ) 函数. 在 #temptable 临时表中的记录总数除以每页数据条数，并将结果转换为 decimal(16,2) 类型，然后使用 CEILING()函数将结果向上舍入得到总页数.

d. 使用 BETWEEN 运算符查询预期范围的记录.

在这里，可以使用 BETWEEN 运算符查询 ROW_NUMBER 列的值在页内的范围之间的记录. 具体地，计算起始行和结束行，然后查询两列之间的所有行.

e. 清空临时表。

使用DROP TABLE 语句清空#temptable表.

1. 测试存储过程

DECLARE @pageCount INT

EXEC [dbo].[p_Page] @pageIndex=1, @pageSize=10, @pageCount=@pageCount OUTPUT

SELECT * FROM #temptable

SELECT @pageCount

预期结果如下：

• 显示第一页的第1-10条数据
• 输出总页数

注意，在运行以上的示例代码之前，请确保临时表已经被删除. 否则，可能会得到以下错误消息：“There is already an object named '#temptable' in the database.”.