在SQL Server数据库操作中,我们常常会用到存储过程对实现对查询的数据的分页处理,以方便浏览者的浏览。本文我们总结了五种SQL Server分页存储过程的方法,并对其性能进行了比较,接下来就让我们来一起了解一下这一过程。
创建数据库data_Test :
?1234567891011121314151617181920212223 | create data_Test GO use GO create tb_TestTable --创建表 ( id int
primary
, userName not
, userPWD not
, userEmail null ) GO |
插入数据:
?1234567891011121314151617 | set
on declare
count set @ count =1 while count <=2000000 begin insert tb_TestTable(id,userName,userPWD,userEmail) values (@ count , 'admin' , 'admin888' , 'lli0077@yahoo.com.cn' ) set
count =@ count +1 end set
off |
1、利用select top 和select not in进行分页
具体代码如下:
?12345678910111213141516171819202122232425262728293031 | create proc_paged_with_notin --利用select top and select not in ( @pageIndex int , --页索引 @pageSize int
) as begin set
on ; declare
--耗时 declare
select
set
'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
as
set
off ; end |
2、利用select top 和 select max(列键)
?12345678910111213141516171819202122232425262728293031 | create proc_paged_with_selectMax --利用select top and select max(列) ( @pageIndex int , --页索引 @pageSize int
) as begin set
on ; declare
declare
select
set
'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
as
set
off ; end |
3、利用select top和中间变量
?12345678910111213141516171819202122232425262728293031323334353637 | create proc_paged_with_Midvar --利用ID>最大ID值和中间变量 ( @pageIndex int , @pageSize int ) as declare
count declare
int declare
declare
begin set
on ; select
count =0,@ID=0,@timediff=getdate() select
count =@ count +1,@ID= case @ count <=@pageSize*@pageIndex then
else
end tb_testTable order id set
'select top ' +str(@pageSize)+ ' * from tb_testTable where ID>' +str(@ID) execute (@sql) select
as
set
off ; end |
4、利用Row_number() 此方法为SQL server 2005中新的方法,利用Row_number()给数据行加上索引
?123456789101112131415161718192021222324252627 | create proc_paged_with_Rownumber --利用SQL 2005中的Row_number() ( @pageIndex int , @pageSize int ) as declare
begin set
on ; select
select
from
select
order ID asc ) as
from
as
where
and
select
as
set
off ; end |
5、利用临时表及Row_number
?1234567891011121314151617181920212223242526272829303132333435363738394041 | create proc_CTE --利用临时表及Row_number ( @pageIndex int , --页索引 @pageSize int
) as set
on ; declare
declare
declare
begin select
set
'with Table_CTE as (select +str(@pageSize)+ ') as page_num,* from tb_TestTable)' ; set
' select * From Table_CTE where page_num=' +str(@pageIndex) end begin execute
select
set
off ; end |
以上的五种方法中,网上说第三种利用select top和中间变量的方法是效率最高的。
关于SQL Server数据库分页的存储过程的五种方法及性能比较的知识就介绍到这里了,希望对大家的学习有所帮助。