浅谈基于SQL Server分页存储过程五种方法及性能比较

在SQL Server数据库操作中，我们常常会用到存储过程对实现对查询的数据的分页处理，以方便浏览者的浏览。
创建数据库data_Test ：

create database data_TestGOuse data_TestGOcreate 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 ondeclare @count intset@count=1while @count<=2000000begininsert into tb_TestTable（id,userName,userPWD,userEmail） values（@count,"admin","admin888","lli0077@yahoo.com.cn"）set @count=@count+1endset 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--每页记录数）asbeginset 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后不支技直接接参数，所以写成了字符串@sqlselect 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--页记录数）asbeginset nocount on;declare @timediff datetimedeclare @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）asdeclare @count intdeclare @ID intdeclare @timediff datetimedeclare @sql nvarchar（500）beginset 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 idset @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）asdeclare @timediff datetimebeginset 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--页记录数）asset nocount on;declare @ctestr nvarchar（）declare @strSql nvarchar（）declare @datediff datetimebeginselect @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）endbeginexecute sp_executesql @strSqlselect datediff（ms,@datediff,GetDate（））set nocount off;end

以上的五种方法中，网上说第三种利用select top和中间变量的方法是效率最高的。关于SQL Server分页存储过程五种方法及性能比较的全部内容就到此结束了，希望对大家有所帮助。