SQL Server 存储过程的分页方案比拼

1 SQLServer存储过程的分页，这个问题已经讨论过几年了，很多朋友在问我，所以在此发表一下我的观点
2 建立表：
3
4 CREATE TABLE [ TestTable ] (
5 [ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
6 [ FirstName ] [ nvarchar ] ( 100 )COLLATEChinese_PRC_CI_AS NULL ,
7 [ LastName ] [ nvarchar ] ( 100 )COLLATEChinese_PRC_CI_AS NULL ,
8 [ Country ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NULL ,
9 [ Note ] [ nvarchar ] ( 2000 )COLLATEChinese_PRC_CI_AS NULL
10 ) ON [ PRIMARY ]
11 GO
12
13
14
15 插入数据：(2万条，用更多的数据测试会明显一些)
16 SET IDENTITY_INSERT TestTable ON
17
18 declare @i int
19 set @i = 1
20 while @i <= 20000
21 begin
22 insert into TestTable( [ id ] ,FirstName,LastName,Country,Note) values ( @i , ' FirstName_XXX ' , ' LastName_XXX ' , ' Country_XXX ' , ' Note_XXX ' )
23 set @i = @i + 1
24 end
25
26 SET IDENTITY_INSERT TestTable OFF
27
28
29
30 -- -----------------------------------
31
32 分页方案一：(利用NotIn和SELECTTOP分页)
33 语句形式：
34 SELECT TOP 10 *
35 FROM TestTable
36 WHERE (ID NOT IN
37 ( SELECT TOP 20 id
38 FROM TestTable
39 ORDER BY id))
40 ORDER BY ID
41
42
43 SELECT TOP 页大小 *
44 FROM TestTable
45 WHERE (ID NOT IN
46 ( SELECT TOP 页大小 * 页数id
47 FROM 表
48 ORDER BY id))
49 ORDER BY ID
50
51 -- -----------------------------------
52
53 分页方案二：(利用ID大于多少和SELECTTOP分页）
54 语句形式：
55 SELECT TOP 10 *
56 FROM TestTable
57 WHERE (ID >
58 ( SELECT MAX (id)
59 FROM ( SELECT TOP 20 id
60 FROM TestTable
61 ORDER BY id) AS T))
62 ORDER BY ID
63
64
65 SELECT TOP 页大小 *
66 FROM TestTable
67 WHERE (ID >
68 ( SELECT MAX (id)
69 FROM ( SELECT TOP 页大小 * 页数id
70 FROM 表
71 ORDER BY id) AS T))
72 ORDER BY ID
73
74
75 -- -----------------------------------
76
77 分页方案三：(利用SQL的游标存储过程分页)
78 create procedure XiaoZhengGe
79 @sqlstr nvarchar ( 4000 ), -- 查询字符串
80 @currentpage int , -- 第N页
81 @pagesize int -- 每页行数
82 as
83 set nocount on
84 declare @P1 int , -- P1是游标的id
85 @rowcount int
86 exec sp_cursoropen @P1 output, @sqlstr , @scrollopt = 1 , @ccopt = 1 , @rowcount = @rowcount output
87 select ceiling ( 1.0 * @rowcount / @pagesize ) as 总页数 -- ,@rowcountas总行数,@currentpageas当前页
88 set @currentpage = ( @currentpage - 1 ) * @pagesize + 1
89 exec sp_cursorfetch @P1 , 16 , @currentpage , @pagesize
90 exec sp_cursorclose @P1
91 set nocount off
92
93 其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
94 建议优化的时候，加上主键和索引，查询效率会提高。
95
96 通过SQL查询分析器，显示比较：我的结论是:
97 分页方案二：(利用ID大于多少和SELECTTOP分页）效率最高，需要拼接SQL语句
98 分页方案一：(利用NotIn和SELECTTOP分页)效率次之，需要拼接SQL语句
99 分页方案三：(利用SQL的游标存储过程分页)效率最差，但是最为通用
100
101 在实际情况中，要具体分析。