浅谈基于SQL Server分页存储过程五种方法及性能比较

在sql server数据库操作中，我们常常会用到存储过程对实现对查询的数据的分页处理，以方便浏览者的浏览。

创建数据库data_test ：

create database data_test  
go  
use data_test  
go  
create table tb_testtable  --创建表  
(  
id int identity(1,1) primary key,  
username nvarchar(20) not null,  
userpwd nvarchar(20) not null,  
useremail nvarchar(40) null  
)  
go 

插入数据：

set identity_insert tb_testtable on  
declare @count int  
set@count=1  
while @count<=2000000  
begin  
insert into tb_testtable(id,username,userpwd,useremail) values(@count,'admin','admin888','[e-mail]')  
set @count=@count+1  
end  
set identity_insert tb_testtable off 

1、利用select top 和select not in进行分页

具体代码如下：

create procedure proc_paged_with_notin --利用select top and select not in  
(  
@pageindex int, --页索引  
@pagesize int  --每页记录数  
)  
as  
begin  
set nocount on;  
declare @timediff datetime --耗时  
declare @sql nvarchar(500)  
select @timediff=getdate()  
set @sql='select top '+str(@pagesize)+' * from tb_testtable where(id not in(select top '+str(@pagesize*@pageindex)+' id from tb_testtable order by id asc)) order by id'  
execute(@sql) --因select top后不支技直接接参数，所以写成了字符串@sql  
select datediff(ms,@timediff,getdate()) as 耗时  
set nocount off;  
end 

2、利用select top 和 select max(列键)

create procedure proc_paged_with_selectmax --利用select top and select max(列)  
(  
@pageindex int, --页索引  
@pagesize int  --页记录数  
)  
as  
begin  
set nocount on;  
declare @timediff datetime  
declare @sql nvarchar(500)  
select @timediff=getdate()  
set @sql='select top '+str(@pagesize)+' * from tb_testtable where(id>(select max(id) from (select top '+str(@pagesize*@pageindex)+' id from tb_testtable order by id) as temptable)) order by id'  
execute(@sql)  
select datediff(ms,@timediff,getdate()) as 耗时  
set nocount off;  
end 

3、利用select top和中间变量

create procedure proc_paged_with_midvar --利用id>最大id值和中间变量  
(  
@pageindex int,  
@pagesize int  
)  
as  
declare @count int  
declare @id int  
declare @timediff datetime  
declare @sql nvarchar(500)  
begin  
set nocount on;  
select @count=0,@id=0,@timediff=getdate()  
select @count=@count+1,@id=case when @count<=@pagesize*@pageindex then id else @id end from tb_testtable order by id  
set @sql='select top '+str(@pagesize)+' * from tb_testtable where id>'+str(@id)  
execute(@sql)  
select datediff(ms,@timediff,getdate()) as 耗时  
set nocount off;  
end 

4、利用row_number() 此方法为sql server 2005中新的方法,利用row_number()给数据行加上索引

create procedure proc_paged_with_rownumber --利用sql 2005中的row_number()  
(  
@pageindex int,  
@pagesize int  
)  
as  
declare @timediff datetime  
begin  
set nocount on;  
select @timediff=getdate()  
select * from (select *,row_number() over(order by id asc) as idrank from tb_testtable) as idwithrownumber where idrank>@pagesize*@pageindex and idrank<@pagesize*(@pageindex+1)  
select datediff(ms,@timediff,getdate()) as 耗时  
set nocount off;  
end

5、利用临时表及row_number

create procedure proc_cte --利用临时表及row_number  
(  
@pageindex int, --页索引  
@pagesize int  --页记录数  
)  
as  
set nocount on;  
declare @ctestr nvarchar()  
declare @strsql nvarchar()  
declare @datediff datetime  
begin  
select @datediff=getdate()  
set @ctestr='with table_cte as  
(select ceiling((row_number() over(order by id asc))/'+str(@pagesize)+') as page_num,* from tb_testtable)';  
set @strsql=@ctestr+' select * from table_cte where page_num='+str(@pageindex)  
end  
begin  
execute sp_executesql @strsql  
select datediff(ms,@datediff,getdate())  
set nocount off;  
end

以上的五种方法中，网上说第三种利用select top和中间变量的方法是效率最高的。关于sql server分页存储过程五种方法及性能比较的全部内容就到此结束了，希望对大家有所帮助。