sqlserver 分页方法

本文分别介绍三种sqlserver分页方式。并开启statistics做为统计。

开启统计：

--清理
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--开启统计信息
SET STATISTICS TIME ON
SET STATISTICS IO ON

使用top

方法一：

优点：通用，第一页可以直接把第二个数字2设置为0即可。统计结果表明，主要的性能消耗在IO上。对比了方法二后，建议如果坚持使用top，还是用【方法二】会比较好——方法二的逻辑读取次数会小很多。

缺点：两层嵌套，对于大表还是压力很大的。而且读取次数很多。

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--开启统计信息
SET STATISTICS TIME ON
SET STATISTICS IO ON

SELECT TOP 2 * FROM vvtest WHERE ID NOT IN --这里的2：每页展示的数量
(SELECT TOP 2 ID FROM vvtest ORDER BY id) --这里的2=页码*每页展示的数量，这里是1*2。
ORDER BY id

方法二（如用top时，建议使用）：

优点：逻辑比较直白的，适合小白入门。逻辑读取次数不会根据页码不同而变。查看统计结果后，主要的性能消耗还是在IO。

缺点：脚本三层嵌套很冗余。而且第一页的脚本不能复用。要单独写。

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--开启统计信息
SET STATISTICS TIME ON
SET STATISTICS IO ON

select top 2 id,name from vvtest where id>=--这里的2是每页展示的数量
(select max(id) from (select top 2 id from vvtest order by id ) --这里的2=页码*每页数量，页码不能为0
tmp_max_table)order by id 

 

使用row_number

优点：性能比top方式好。

缺点：还是需要使用两层嵌套。代码看起来会较复杂。而且执行起来发现性能变化还蛮大的，主要变化在IO消耗的时间上。（下图的统计信息只是其中一次。不作为比较性能的直接证据）

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--开启统计信息
SET STATISTICS TIME ON
SET STATISTICS IO ON

select top 2 * --这里的2是每页展示的数量
from (select row_number()
over(order by id asc) as rownumber,*
from vvtest) temp_row
where rownumber>((1-1)*2);--这里对应的公式是：（页码-1）*每页展示的数量，页码从1开始。

 

使用row fetch next

优：代码很简单。

缺：sqlserver 2012版本以上才能使用。

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--开启统计信息
SET STATISTICS TIME ON
SET STATISTICS IO ON

SELECT [id]
      ,[name]
  FROM [master].[dbo].[vvtest]
  order by name  desc offset (3-1)*2 row fetch next 2 row only --这里对应的公式是：(页码-1)*每页展示的数量，页码从1开始。