SQL SERVER 2008分页

浏览数：22 / 时间：2015年06月12日

今天准备把项目中用到的分页功能增加一下，原来的太局限了，于是上网找了一些资料记录下来，前面的是利用别人总结的，后面是自己测试结果，哈哈，测试要有依据：

SQL SERVER 2008 中三种分页方法与比较，需要的朋友可以参考一下

建立表：

代码如下:

CREATE TABLE [TestTable] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
插入数据：(20万条，用更多的数据测试会明显一些)
SET IDENTITY_INSERT TestTable ON
declare @i int
set @i=1
while @i<=200000
begin
    insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, ‘FirstName_XXX‘,‘LastName_XXX‘,‘Country_XXX‘,‘Note_XXX‘)
    set @i=@i+1
end
SET IDENTITY_INSERT TestTable OFF

分页方案一：(利用Not In和SELECT TOP分页)

语句形式：

代码如下:

SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 20 id
         FROM TestTable
         ORDER BY id))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 页大小*页数 id
         FROM 表
         ORDER BY id))
ORDER BY ID

分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：

代码如下:

SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 页大小*页数 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID

分页方案三：(利用SQL的游标存储过程分页)

代码如下:

create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。

建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用
在实际情况中，要具体分析。

以上是别人写的文章，在这里我作为保存一下，防止以后原文找不到了，下面就是自己写的：

我只是测试了第二种和第三种方案，发现第二种方案确实很快,随着数据的增加，相差不是一个数量级的，曾经在一本书上看到过关于游标的，效率确实不如直接执行sql的，方案二在用的时候需要注意id，这里的id是自增的，如果没有自增的，就需要引入rownum了。这个时候sql可能会变得更加复杂。

自己结合了方案二写了一个分页，因为有些表要是没有自增长的字段，分页二实现起来就会有难度，于是自己利用sql server的row_number()函数来实现了一下

SELECT TOP 页大小 * FROM (select row_number()over(order by ID) as rn,* from TestTable )temTable where rn >= 页大小*(页码-1) and rn <页大小*页码

该语句来自oracle的分页想法，因为这个可以在程序中封装，当然了，效率上还是没有方案二高，而且要求必须要有排序字段。我测试的数据是95万条，11个字段。

条件是：where rn > 100000 and rn<101000

自己写的测试结果：

SQL Server 分析和编译时间:
CPU 时间 = 3 毫秒，占用时间 = 3 毫秒。

(999 行受影响)
表 ‘memberlevelglide‘。扫描计数 1，逻辑读取 101196 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(8 行受影响)

SQL Server 执行时间:
CPU 时间 = 156 毫秒，占用时间 = 531 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。