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这样索引就失效了，可能从效率最高掉到效率最低
转载于:https://www.cnblogs.com/szRoyman/archive/2006/01/15/317509.html
扫一扫
244
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
