存储过程分页探究

首先新建一个数据表并插入数据，为存储过程分页研究提供数据支持。本文批量插入2000000条数据插入脚本如下：

declare @Count int 
set @Count=0
while @Count<2000000
begin
    insert into Teacher (Name,Sex,City,BirthDay) values('张三',1,'陕西西安',getdate())
    set @Count=@Count+1    
end

主键采用自动生成的方式，不在上述脚本里面。由于采用数据库分页的方式只需要加载需要显示的数据而不需要一次加载所有的数据，从而达到提高页面加载速度的目的，所以下面来研究、比较和总结几种存储过程分页的方法，以供借鉴和查找。

1.利用select top 和select not in

create procedure PagerWithNotIN
    @PageIndex int,    --页索引
    @PageSize int    --页大小
as 
begin
    set nocount on
    declare @TimeDiff datetime
    declare @sql varchar(1000)
    select @TimeDiff=getdate()
    set @sql='select top '+str(@PageSize)+' * from Teacher where ID not in 
    (select top '+str(@PageSize*@PageIndex)+' ID from Teacher order by ID asc) order by ID'
    execute(@sql)
    select datediff(ms,@TimeDiff,getdate()) as 时间
    set nocount off
end

2.利用select top 和 select max(列键)，注意降序使用min(列键)

create procedure PagerWithSelectMax
    @PageIndex int,    --页索引
    @PageSize int    --页大小
as 
begin
    set nocount on
    declare @TimeDiff datetime
    declare @sql varchar(1000)
    select @TimeDiff=getdate()
    set @sql='select top '+str(@PageSize)+' * from Teacher where ID>(select max(ID) from 
    (select top '+str(@PageSize*@PageIndex)+' ID from Teacher order by ID asc) temp) order by ID'
    execute(@sql)
    select datediff(ms,@TimeDiff,getdate()) as 时间
    set nocount off
end

3.利用row_number（）方法，注意SQL Server 2005之后均有该方法

CREATE procedure [dbo].[PagerWithRowNumber]
    @PageIndex int,    --页索引
    @PageSize int    --页大小
as 
begin
    set nocount on
    declare @TimeDiff datetime
    select @TimeDiff=getdate()
    select * from 
    (select *,row_number() over(order by ID asc) as RowNumber from Teacher)
    as temp where RowNumber>@PageSize*@PageIndex and RowNumber<=@PageSize*(@PageIndex+1)
    select datediff(ms,@TimeDiff,getdate()) as 时间
    set nocount off
end

此外，还有利用中间变量、临时表等方法均可以实现存储过程分页。个人认为效率不及上述三种，在此不再罗列。以后如有发现更好的方法，继续补充进来。闲话不说，下面列出上面三种存储过程分页的测试结果(以每页20条为准，每页测试3次取平均值)：

存储过程分页	第2页耗时	第100页耗时	第1000页耗时	第10000页耗时	第50000页耗时	第99999页耗时	效率排行
利用select top 和select not in
利用select top 和 select max(列键)
利用row_number（）方法