SQL Server 大量数据的分页存储过程代码

OK,我们首先创建一数据库：data_Test,并在此数据库中创建一表：tb_TestTable create database data_Test --创建数据库data_Test GO use data_Test GO create table tb_TestTable --创建表 ( id int identity(1,1) primary key, userName nvarchar(20) not nu

 　　OK,我们首先创建一数据库：data_Test,并在此数据库中创建一表：tb_TestTable

　　create database data_Test --创建数据库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

　　然后我们在数据表中插入2000000条数据:

　　--插入数据

　　set identity_insert tb_TestTable on

　　declare @count int

　　set @count=1

　　while @count

　　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进行分页，具体代码如下：

　　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

　　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

　　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

　　OK,至此，存储过程创建完毕，，我们分别在每页10条数据的情况下在第2页，第1000页，第10000页，第100000页，第199999页进行测试，耗时单位：ms 每页测试5次取其平均值