SQL Server 存储过程的分页

 

SQL Server 存储过程的分页方案比拼 
SQL Server 存储过程的分页，这个问题已经讨论过几年了，很多朋友在问我，所以在此发表一下我的观点
建立表：

CREATE TABLE [TestTable] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

 

插入数据：(2万条，用更多的数据测试会明显一些)
SET IDENTITY_INSERT TestTable ON

declare @i int
set @i=1
while @i<=20000
begin
    insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
    set @i=@i+1
end

SET IDENTITY_INSERT TestTable OFF

 

-------------------------------------

分页方案一：(利用Not In和SELECT TOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 20 id
         FROM TestTable
         ORDER BY id))
ORDER BY ID


SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 页大小*页数 id
         FROM 表
         ORDER BY id))
ORDER BY ID

-------------------------------------

分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID


SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 页大小*页数 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID


-------------------------------------

分页方案三：(利用SQL的游标存储过程分页)
create  procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
 @rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1
set nocount off

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页)   效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用

在实际情况中，要具体分析。


更多的讨论见：
http://community.csdn.net/Expert/topic/3292/3292678.xml?temp=.1621515




Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=103511

[点击此处收藏本文]   发表于 2004年09月14日 1:05 AM 


  
squirrel_sc 发表于2004-09-28 8:38 PM  IP: 61.49.220.*
能否给出具体的测试结果数据呢？谢谢~

  
QQ'Richer 发表于2004-10-29 10:12 AM  IP: 219.239.44.*
好，总算找到这篇文章了，谢谢洪根兄

  
SUN 发表于2004-11-09 10:49 AM  IP: 61.233.144.*
declare @pagesize int 
set @pagesize = 10 
select top @pagesize * from album 
order by picid 

这样怎么不对啊？ 
老大！ 
select top 10 * from album 
order by picid 
又可以 为什么呢？ 
那我怎么写存储过程啊！

  
SUN 发表于2004-11-09 11:06 AM  IP: 61.233.144.*
那我 不是 不能写成 存储过程？ 
只有写成 SQL语句放在页面上？ 
这样不是会降低性能？

  
hubinasm 发表于2004-11-21 11:51 PM  IP: 
TrackBack来自《关于数据库分页》：

Ping Back来自：blog.csdn.net

  
sunny 发表于2004-12-02 10:25 PM  IP: 61.149.250.*
第二种办法，如果按照主键排序可以。 

不然的话不行。 


  
菩提树 发表于2004-12-08 6:06 PM  IP: 219.133.133.*
很多情况下,我们可能不能按主键来排序并分页,比如,你可能需要按工资高低给20万条记录分页及排序,那你肯定只能用临时表或者游标了,游标我不熟,临时表倒是可以达到目的,可是效率不好呀 
ALTER PROCEDURE dbo.GetPageRecord 
( 
@tblName varchar(255), -- 表名 
@fldName varchar(255), --排序字段 
@KeyField varchar(255), --主键 
@PageSize int = 10, -- 页尺寸 
@PageIndex int = 1, -- 页码 
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回 
@OrderType bit = 1, -- 设置排序类型, 非 0 值则降序 
@strWhere varchar(4000) = '' -- 查询条件 (注意: 不要加 where) 
) 
AS 
SET NOCOUNT ON 
DECLARE @PageLowerBound int 
DECLARE @PageUpperBound int 

-- Set the page bounds 
SET @PageLowerBound = @PageSize * @PageIndex 
SET @PageUpperBound = @PageLowerBound + @PageSize + 1 

-- Create a temp table to store the select results 
CREATE TABLE #tmp 
( 
RecNo int IDENTITY (1, 1) NOT NULL, 
oldid int 
) 
--generate record 
Declare @Sqlstr varchar(6000) 
set @sqlstr='select '+@Keyfield+' from '+@tblname 
if(@strWhere<>'') 
begin 
set @Sqlstr=@sqlstr+' where ('+@strWhere+')' 
end 
set @sqlstr=@sqlstr+' order by '+@fldName 
if(@ordertype=0) 
begin 
set @sqlstr=@sqlstr+' asc' 
end 
else 
begin 
set @sqlstr=@sqlstr+' desc' 
end 
set @sqlstr='insert into #tmp (oldid) '+@sqlstr 
execute(@sqlstr) 
set @sqlstr='SELECT TableA.* FROM '+@tblname+' TableA (nolock), #tmp T WHERE T.oldid = TableA.'+@keyfield+' AND T.RecNo > '+ cast(@PageLowerBound as varchar)+' AND T.RecNo < '+cast(@PageUpperBound as varchar)+' ORDER BY T.RecNo' 
execute(@sqlstr)

  
菩提树 发表于2004-12-08 6:06 PM  IP: 219.133.133.*
ALTER PROCEDURE dbo.GetPageRecord 
( 
@tblName varchar(255), -- 表名 
@fldName varchar(255), --排序字段 
@KeyField varchar(255), --主键 
@PageSize int = 10, -- 页尺寸 
@PageIndex int = 1, -- 页码 
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回 
@OrderType bit = 1, -- 设置排序类型, 非 0 值则降序 
@strWhere varchar(4000) = '' -- 查询条件 (注意: 不要加 where) 
) 
AS 
SET NOCOUNT ON 
DECLARE @PageLowerBound int 
DECLARE @PageUpperBound int 

-- Set the page bounds 
SET @PageLowerBound = @PageSize * @PageIndex 
SET @PageUpperBound = @PageLowerBound + @PageSize + 1 

-- Create a temp table to store the select results 
CREATE TABLE #tmp 
( 
RecNo int IDENTITY (1, 1) NOT NULL, 
oldid int 
) 
--generate record 
Declare @Sqlstr varchar(6000) 
set @sqlstr='select '+@Keyfield+' from '+@tblname 
if(@strWhere<>'') 
begin 
set @Sqlstr=@sqlstr+' where ('+@strWhere+')' 
end 
set @sqlstr=@sqlstr+' order by '+@fldName 
if(@ordertype=0) 
begin 
set @sqlstr=@sqlstr+' asc' 
end 
else 
begin 
set @sqlstr=@sqlstr+' desc' 
end 
set @sqlstr='insert into #tmp (oldid) '+@sqlstr 
execute(@sqlstr) 
set @sqlstr='SELECT TableA.* FROM '+@tblname+' TableA (nolock), #tmp T WHERE T.oldid = TableA.'+@keyfield+' AND T.RecNo > '+ cast(@PageLowerBound as varchar)+' AND T.RecNo < '+cast(@PageUpperBound as varchar)+' ORDER BY T.RecNo' 
execute(@sqlstr)

  
3doyang 发表于2005-02-15 7:14 PM  IP: 
TrackBack来自《ASP.NET分页的处理方式》：

Ping Back来自：blog.csdn.net

  
Nill 发表于2005-03-05 1:44 AM  IP: 219.128.247.*
我改了下方案2 

declare @pagesize int 
declare @pagenum int 
set @pagenum=3---第几页 
set @pagesize=20 ----每页显示条目数 

declare @t0 varchar(10) 
declare @t1 varchar(10) 

set @t0=cast(@pagesize as varchar(10)) 
set @t1=cast(@pagesize*(@pagenum-1) as varchar(10)) 
--加了个判断是否是第一页。 
if @pagenum<>1 
exec('SELECT TOP '+@t0+ ' * FROM DV_board WHERE (boardid >(SELECT MAX(boardid) FROM (SELECT TOP '+@t1+ ' boardid FROM DV_board ORDER BY boardid) AS T)) ORDER BY boardID') 
else 
exec('SELECT TOP '+@t0+ ' * FROM DV_board')

  
曾登高 发表于2005-03-28 1:11 AM  IP: 
TrackBack来自《近日关注：系统性能提升之道--内存镜像表》：

Ping Back来自：blog.csdn.net

  
我是好猫 发表于2005-03-29 8:49 PM  IP: 218.58.242.*
什么时候，盖子能推出个 适合分页（类似 mysql 的 LIMIT ）的 新mssql版本啊 。。。。 

  
九命鸟 发表于2005-04-20 5:10 PM  IP: 210.22.100.*
这样分页的方例都不是很通用，可以考虑用Rowcount的方法，参数@table, @fields, @filter, @orderby, @groupby, @pagenumber, @pagesize,在SP中形成SQL语句。

  
随身影子 发表于2005-04-22 12:48 PM  IP: 219.137.167.*
如果输出按字段AA排序，并且 AA 是索引的话，用游标是最高效率的。用游标分两次分别取到Begin和End点的AA的值就可以搞定。 


  
王者归来 发表于2005-06-01 10:54 PM  IP: 61.186.252.*
两个问题：1.用方法2，如何选择第一页的数据，pagesize和pagecount分别怎么传？ 2.如何实现主从表数据的分页?

  
王者归来 发表于2005-06-01 10:57 PM  IP: 61.186.252.*
To 九命鸟：老兄说的这种方法哪里可以找到参考？

  
ξσ Dicky σξ 发表于2005-06-18 10:36 AM  IP: 
TrackBack来自《SQL Server 存储过程的分页方案比拼》：

Ping Back来自：blog.csdn.net

  
leeight 发表于2005-07-12 10:38 AM  IP: 
TrackBack来自《关于存储过程实现分页的技术》：

Ping Back来自：blog.csdn.net

  
小灰 发表于2005-07-21 8:59 PM  IP: 61.186.252.*
看看这个效率如何？怎么改进？ 
CREATE proc page 
@RecordCount int output, 
@QueryStr nvarchar(100)='table1',--表名、视图名、查询语句 
@PageSize int=20, --每页的大小(行数) 
@PageCurrent int=2, --要显示的页 从0开始 
@FdShow nvarchar (1000)='*', --要显示的字段列表 
@IdentityStr nvarchar (100)='id', --主键 
@WhereStr nvarchar (200)='1=1', 
@FdOrder nvarchar(100)='desc' --排序 只能取desc或者asc 
as 
declare 
@sql nvarchar(2000) 
set @sql = '' 
if @WhereStr = '' 
set @WhereStr = '1=1' 

if @PageCurrent = 0 begin 
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder 
end 

else begin 
if upper(@FdOrder) = 'DESC' begin 
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc' 
end 
else begin 
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc' 
end 
end 
--print @sql 
execute(@sql) 


if(@RecordCount is null or @RecordCount<=0)begin 
declare @tsql nvarchar(200) 
set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr 
exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output 
select @Recordcount 
end 
GO 


  
.., 发表于2005-08-15 3:27 AM  IP: 61.186.252.*
愚以为前两种方法只对主键字段的排序有效。 
ID NAME TYPEID 
1 aaa 1 
2 bbb 2 
3 ccc 3 
4 ddd 3 
5 eee 4 
6 fff 5 

例如有如上6条数据，ID为主键，TYPEID为外部键（索引）。 
如果使用ID排序，则可得正确结果。 
但是如果使用TYPEID排序的话，每页显示3条数据，要显示第2页。则两种方法都只能显示2条记录，而实际应该是3条。 

第3种方法，由于在下对sql server的cursor不熟悉，所以不清楚结果。

  
cnatang 发表于2005-09-13 3:04 PM  IP: 211.100.21.*
是啊。要实现一个完美的分页好象不可能？？ 
用方法2 ，我现在就碰到了 order by 非主键里的问题！ 
搞了一晚上，始终没有搞掂。楼主有什么好的解决办法？

  
zxub 发表于2005-10-13 10:21 PM  IP: 211.100.21.*
　　order by非主键的话，就用主键字段+排序字段，我就不信不能排，我做C#分页插件的时候就是这么做的，没问题的。

  
网教中国 发表于2005-12-08 10:09 AM  IP: 222.79.23.*
不错

  
oracle 发表于2006-01-05 10:31 AM  IP: 61.154.121.*
都不好，大多数情况下不会用主键来排序，再说拼接sql这样索引就失效了，可能从效率最高掉到效率最低