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