浅谈基于SQL Server分页存储过程五种方法及性能比较
内容摘要
在SQL Server数据库操作中,我们常常会用到存储过程对实现对查询的数据的分页处理,以方便浏览者的浏览。
创建数据库data_Test :
create database data_Test
GO
use data_
创建数据库data_Test :
create database data_Test
GO
use data_
文章正文
在SQL Server数据库操作中,我们常常会用到存储过程对实现对查询的数据的分页处理,以方便浏览者的浏览。
创建数据库data_Test :
1 2 3 4 5 6 7 8 9 10 11 12 | 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 |
插入数据:
1 2 3 4 5 6 7 8 9 | 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' , 'lli0077@yahoo.com.cn' ) set @ count =@ count +1 end set identity_insert tb_TestTable off |
1、利用select top 和select not in进行分页
具体代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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(列键)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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和中间变量
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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()给数据行加上索引
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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分页存储过程五种方法及性能比较的全部内容就到此结束了,希望对大家有所帮助。
代码注释