SQL Server分页存储过程的五种方法及性能比较

数据库 SQL Server
本文主要介绍了SQL Server数据库分页的存储过程的五种方法以及它们之间性能的比较,并给出了详细的代码,希望能够对您有所帮助。

SQL Server数据库操作中,我们常常会用到存储过程对实现对查询的数据的分页处理,以方便浏览者的浏览。本文我们总结了五种SQL Server分页存储过程的方法,并对其性能进行了比较,接下来就让我们来一起了解一下这一过程。

创建数据库data_Test :

  1. create database data_Test    
  2.  
  3. GO    
  4.  
  5. use data_Test    
  6.  
  7. GO    
  8.  
  9. create table tb_TestTable   --创建表    
  10.  
  11. (    
  12.  
  13. id int identity(1,1) primary key,    
  14.  
  15. userName nvarchar(20) not null,    
  16.  
  17. userPWD nvarchar(20) not null,    
  18.  
  19. userEmail nvarchar(40) null   
  20.  
  21. )    
  22.  
  23. GO 

插入数据:

  1. set identity_insert tb_TestTable on    
  2.  
  3. declare @count int   
  4.  
  5. set@count=1    
  6.  
  7. while @count<=2000000    
  8.  
  9. begin    
  10.  
  11. insert into tb_TestTable(id,userName,userPWD,userEmail) values(@count,'admin','admin888','lli0077@yahoo.com.cn')    
  12.  
  13. set @count=@count+1    
  14.  
  15. end    
  16.  
  17. set identity_insert tb_TestTable off 

1、利用select top 和select not in进行分页

具体代码如下:

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

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

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

3、利用select top和中间变量

  1. create procedure proc_paged_with_Midvar  --利用ID>最大ID值和中间变量    
  2.  
  3. (    
  4.  
  5. @pageIndex int,    
  6.  
  7. @pageSize int   
  8.  
  9. )    
  10.  
  11. as   
  12.  
  13. declare @count int   
  14.  
  15. declare @ID int   
  16.  
  17. declare @timediff datetime    
  18.  
  19. declare @sql nvarchar(500)    
  20.  
  21. begin    
  22.  
  23. set nocount on;    
  24.  
  25. select @count=0,@ID=0,@timediff=getdate()    
  26.  
  27. select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id    
  28.  
  29. set @sql='select top '+str(@pageSize)+' * from tb_testTable where ID>'+str(@ID)    
  30.  
  31. execute(@sql)    
  32.  
  33. select datediff(ms,@timediff,getdate()) as 耗时    
  34.  
  35. set nocount off;    
  36.  
  37. end 

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

  1. create procedure proc_paged_with_Rownumber  --利用SQL 2005中的Row_number()    
  2.  
  3. (    
  4.  
  5. @pageIndex int,    
  6.  
  7. @pageSize int   
  8.  
  9. )    
  10.  
  11. as   
  12.  
  13. declare @timediff datetime    
  14.  
  15. begin    
  16.  
  17. set nocount on;    
  18.  
  19. select @timediff=getdate()    
  20.  
  21. 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)    
  22.  
  23. select datediff(ms,@timediff,getdate()) as 耗时    
  24.  
  25. set nocount off;    
  26.  
  27. end  

5、利用临时表及Row_number

  1. create procedure proc_CTE  --利用临时表及Row_number    
  2.  
  3. (    
  4.  
  5. @pageIndex int,  --页索引    
  6.  
  7. @pageSize int    --页记录数    
  8.  
  9. )    
  10.  
  11. as   
  12.  
  13. set nocount on;    
  14.  
  15. declare @ctestr nvarchar(400)    
  16.  
  17. declare @strSql nvarchar(400)    
  18.  
  19. declare @datediff datetime    
  20.  
  21. begin    
  22.  
  23. select @datediff=GetDate()    
  24.  
  25. set @ctestr='with Table_CTE as   
  26.  
  27. (select ceiling((Row_number() over(order by ID ASC))/'+str(@pageSize)+') as page_num,* from tb_TestTable)';    
  28.  
  29. set @strSql=@ctestr+' select * From Table_CTE where page_num='+str(@pageIndex)    
  30.  
  31. end    
  32.  
  33. begin    
  34.  
  35. execute sp_executesql @strSql    
  36.  
  37. select datediff(ms,@datediff,GetDate())    
  38.  
  39. set nocount off;    
  40.  
  41. end 

以上的五种方法中,网上说第三种利用select top和中间变量的方法是效率最高的。

关于SQL Server数据库分页的存储过程的五种方法及性能比较的知识就介绍到这里了,如果您想了解更多SQL Server数据库的知识可以看一下这里的文章:http://database.51cto.com/sqlserver/,相信一定可以带给您收获的!

【编辑推荐】

  1. 存储过程的优缺点及适宜使用的场合详解
  2. 使用connectionStrings连接SQL Server数据库
  3. 操作系统不支持SQL Server服务器组件的解决方案
  4. SQL Server数据库使用DBCC ShowContig检查索引碎片
  5. 一个SQL Server 2005数据库用户sa登录失败的解决方案
责任编辑:赵鹏 来源: 博客园
相关推荐

2010-09-13 13:05:03

sql server分

2011-03-24 13:38:47

SQL Server 存储分页

2011-03-28 10:46:36

sql server存储分页

2011-04-06 15:15:19

2010-09-14 10:47:45

sql server存

2010-06-30 14:36:49

SQL Server

2010-06-18 10:34:38

SQL Server

2010-11-10 15:16:14

Sql Server分

2011-06-17 17:37:16

JavaSQL Server

2010-11-10 13:03:15

SQL Server存

2010-11-09 13:09:58

SQL Server分

2011-07-06 14:18:40

Percona SerMySQL

2010-09-03 15:08:03

SQLselect语句

2010-04-29 15:58:51

Oracle存储过程

2010-09-02 09:37:36

SQL删除

2010-07-19 17:26:55

SQL Server

2017-12-14 10:16:01

CaddySSLDockerNginx

2010-11-09 11:11:12

SQL Server查

2010-09-25 16:00:38

sql存储过程

2010-07-15 12:38:14

SQL Server存
点赞
收藏

51CTO技术栈公众号