五种SQL Server分页存储过程的方法及性能比较

在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(400)  
 
declare @strsql nvarchar(400)  
 
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数据库分页的存储过程的五种方法及性能比较的知识就介绍到这里了，希望对大家的学习有所帮助。