| 01 create procedure proc_paged_with_Midvar --利用ID>最大ID值和中间变量 02 ( 03 @pageIndex int, 04 @pageSize int 05 ) 06 as 07 declare @count int 08 declare @ID int 09 declare @timediff datetime 10 declare @sql nvarchar(500) 11 begin 12 set nocount on; 13 select @count=0,@ID=0,@timediff=getdate() 14 select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id 15 set @sql='select top '+str(@pageSize)+' * from tb_testTable where ID>'+str(@ID) 16 execute(@sql) 17 select datediff(ms,@timediff,getdate()) as 耗时 18 set nocount off; 19 end |
4,利用Row_number() 此方法为sql server 2005中新的方法,利用Row_number()给数据行加上索引
| 01 create procedure proc_paged_with_Rownumber --利用SQL 2005中的Row_number() 02 ( 03 @pageIndex int, 04 @pageSize int 05 ) 06 as 07 declare @timediff datetime 08 begin 09 set nocount on; 10 select @timediff=getdate() 11 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) 12 select datediff(ms,@timediff,getdate()) as 耗时 13 set nocount off; 14 end |
5，利用临时表及Row_number
| 01 create procedure proc_CTE --利用临时表及Row_number 02 ( 03 @pageIndex int, --页索引 04 @pageSize int --页记录数 05 ) 06 as 07 set nocount on; 08 declare @ctestr nvarchar(400) 09 declare @strSql nvarchar(400) 10 declare @datediff datetime 11 begin 12 select @datediff=GetDate() 13 set @ctestr='with Table_CTE as 14 (select ceiling((Row_number() over(order by ID ASC))/'+str(@pageSize)+') as page_num,* from tb_TestTable)'; 15 set @strSql=@ctestr+' select * From Table_CTE where page_num='+str(@pageIndex) 16 end 17 begin 18 execute sp_executesql @strSql 19 select datediff(ms,@datediff,GetDate()) 20 set nocount off; 21 end |
