五种SQL Server分页存储过程的方法及性能比较
在SQLServer数据库操作中，我们常常会用到存储过程对实现对查询的数据的分页处理，以方便浏览者的浏览。本文我们总结了五种SQLServer分页存储过程的方法，并对其性能进行了比较，接下来就让我们来一起了解一下这一过程。
创建数据库data_Test：
createdatabasedata_Test GO usedata_Test GO createtabletb_TestTable--创建表 ( idintidentity(1,1)primarykey, userNamenvarchar(20)notnull, userPWDnvarchar(20)notnull, userEmailnvarchar(40)null ) GO
插入数据：
setidentity_inserttb_TestTableon declare@countint set@count=1 while@count<=2000000 begin insertintotb_TestTable(id,userName,userPWD,userEmail)values(@count,'admin','admin888','lli0077@yahoo.com.cn') set@count=@count+1 end setidentity_inserttb_TestTableoff
1、利用selecttop和selectnotin进行分页
具体代码如下：
createprocedureproc_paged_with_notin--利用selecttopandselectnotin ( @pageIndexint,--页索引 @pageSizeint--每页记录数 ) as begin setnocounton; declare@timediffdatetime--耗时 declare@sqlnvarchar(500) select@timediff=Getdate() set@sql='selecttop'+str(@pageSize)+'*fromtb_TestTablewhere(IDnotin(selecttop'+str(@pageSize*@pageIndex)+'idfromtb_TestTableorderbyIDASC))orderbyID' execute(@sql)--因selecttop后不支技直接接参数，所以写成了字符串@sql selectdatediff(ms,@timediff,GetDate())as耗时 setnocountoff; end
2、利用selecttop和selectmax(列键)
createprocedureproc_paged_with_selectMax--利用selecttopandselectmax(列) ( @pageIndexint,--页索引 @pageSizeint--页记录数 ) as begin setnocounton; declare@timediffdatetime declare@sqlnvarchar(500) select@timediff=Getdate() set@sql='selecttop'+str(@pageSize)+'*Fromtb_TestTablewhere(ID>(selectmax(id)From(selecttop'+str(@pageSize*@pageIndex)+'idFromtb_TestTableorderbyID)asTempTable))orderbyID' execute(@sql) selectdatediff(ms,@timediff,GetDate())as耗时 setnocountoff; end
3、利用selecttop和中间变量
createprocedureproc_paged_with_Midvar--利用ID>最大ID值和中间变量 ( @pageIndexint, @pageSizeint ) as declare@countint declare@IDint declare@timediffdatetime declare@sqlnvarchar(500) begin setnocounton; select@count=0,@ID=0,@timediff=getdate() select@count=@count+1,@ID=casewhen@count<=@pageSize*@pageIndexthenIDelse@IDendfromtb_testTableorderbyid set@sql='selecttop'+str(@pageSize)+'*fromtb_testTablewhereID>'+str(@ID) execute(@sql) selectdatediff(ms,@timediff,getdate())as耗时 setnocountoff; end
4、利用Row_number()此方法为SQLserver2005中新的方法,利用Row_number()给数据行加上索引
createprocedureproc_paged_with_Rownumber--利用SQL2005中的Row_number() ( @pageIndexint, @pageSizeint ) as declare@timediffdatetime begin setnocounton; select@timediff=getdate() select*from(select*,Row_number()over(orderbyIDasc)asIDRankfromtb_testTable)asIDWithRowNumberwhereIDRank>@pageSize*@pageIndexandIDRank<@pageSize*(@pageIndex+1) selectdatediff(ms,@timediff,getdate())as耗时 setnocountoff; end
5、利用临时表及Row_number
createprocedureproc_CTE--利用临时表及Row_number ( @pageIndexint,--页索引 @pageSizeint--页记录数 ) as setnocounton; declare@ctestrnvarchar(400) declare@strSqlnvarchar(400) declare@datediffdatetime begin select@datediff=GetDate() set@ctestr='withTable_CTEas (selectceiling((Row_number()over(orderbyIDASC))/'+str(@pageSize)+')aspage_num,*fromtb_TestTable)'; set@strSql=@ctestr+'select*FromTable_CTEwherepage_num='+str(@pageIndex) end begin executesp_executesql@strSql selectdatediff(ms,@datediff,GetDate()) setnocountoff; end
以上的五种方法中，网上说第三种利用selecttop和中间变量的方法是效率最高的。
关于SQLServer数据库分页的存储过程的五种方法及性能比较的知识就介绍到这里了，希望对大家的学习有所帮助。