大数据量存储分页

五种常用存储过程：

1，利用select top 和select not in进行分页，具体代码如下：

 1   create procedure proc_paged_with_notin  --利用select top and select not in 
 2( 
 3    @pageIndex int,  --页索引 
 4    @pageSize int    --每页记录数 
 5) 
 6as 
 7begin 
 8    set nocount on; 
 9    declare @timediff datetime --耗时 
10    declare @sql nvarchar(500) 
11    select @timediff=Getdate() 
12                 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' 
13    execute(@sql)  --因select top后不支技直接接参数，所以写成了字符串@sql 
14    select datediff(ms,@timediff,GetDate()) as 耗时 
15    set nocount off; 
16end

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

1create procedure proc_paged_with_selectMax  --利用select top and select max(列) 
 2( 
 3    @pageIndex int,  --页索引 
 4    @pageSize int    --页记录数 
 5) 
 6as 
 7begin 
 8set nocount on; 
 9    declare @timediff datetime 
10    declare @sql nvarchar(500) 
11    select @timediff=Getdate() 
12    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' 
13    execute(@sql) 
14    select datediff(ms,@timediff,GetDate()) as 耗时 
15set nocount off; 
16end

3,利用select top和中间变量--此方法因网上有人说效果最佳，所以贴出来一同测试

1create procedure proc_paged_with_Midvar  --利用ID>最大ID值和中间变量 
 2( 
 3    @pageIndex int, 
 4    @pageSize int 
 5) 
 6as 
 7    declare @count int 
 8    declare @ID int 
 9    declare @timediff datetime 
10    declare @sql nvarchar(500) 
11begin 
12set 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 耗时 
18set nocount off; 
19end

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

1create procedure proc_paged_with_Rownumber  --利用SQL 2005中的Row_number() 
 2( 
 3    @pageIndex int, 
 4    @pageSize int 
 5) 
 6as 
 7    declare @timediff datetime 
 8begin 
 9set 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 耗时 
13set nocount off; 
14end

5，利用临时表及Row_number

1create procedure proc_CTE  --利用临时表及Row_number 
 2( 
 3    @pageIndex int,  --页索引 
 4    @pageSize int    --页记录数 
 5) 
 6as 
 7    set nocount on; 
 8    declare @ctestr nvarchar(400) 
 9    declare @strSql nvarchar(400) 
10    declare @datediff datetime 
11begin 
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) 
16end 
17    begin 
18        execute sp_executesql @strSql 
19        select datediff(ms,@datediff,GetDate()) 
20    set nocount off; 
21    end