Sql Server 存储过程分页大全(2005,2000)

1. -----------------------------------------------------------------------------------
2. --作者：启程 www.letwego.cn
3. --只是本人大概测试，不一定完全正确
4. DECLARE @DateBegin datetime
5. DECLARE @DateEnd datetime
6. SET @DateBegin=getdate()
7. --------------------此处的分页存储过程均不是通用的，通用的都要拼接Sql语句------------------
8. -----测试数据：Cpu：Core 1.8，内存：1G， 1百万条，取第 5000 页，每页 100条，结果时间 毫秒
9. --NOT IN (需要主键，需要拼接Sql，速度第二)
10. --EXEC spPage2000 @pageSize=100,@pageIndex=5000,@Counts=0
11. --测试:3080 3016 3236 3173 3186 3233 3203 3123 3216 3060 
12. --三次 ORDER BY (不需要主键，需要拼接Sql，速度第五)
13. --EXEC spPage2001 @pageSize=100,@pageIndex=5000,@Counts=0
14. --测试:42890 52453 48220
15. --临时表 (需要主键，不需要拼接Sql，速度第三)
16. --EXEC spPage2002 @pageSize=100,@pageIndex=5000,@Counts=0
17. --测试:13890 13656 14000
18. --游标 (不需要主键，不需要拼接Sql,最容易做成通用，速度第四)
19. --EXEC spPage2003 @pageSize=100,@pageIndex=5000,@Counts=0
20. --测试:20453 21216 21346
21. --SQL 2005 ROW_NUMBER(不需要主键[有主键更快]，不需要拼接Sql，速度第一)
22. EXEC spPage2005 @pageSize=100,@pageIndex=5000,@Counts=0
23. --有主键测试:830 873 830 890 843 826 830 
24. --无主键测试:15890 14970 15703
25. ---------------------------------------------------------------------
26. SET @DateEnd=getdate()
27. SELECT DATEDIFF(millisecond,@DateBegin,@DateEnd)
28. -----------------------------------------------------------------------------------
29. --表结构
30. CREATE TABLE [dbo].[UserInfo2](
31.     [id] [int] IDENTITY(1,1) NOT NULL,
32.     [user] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
33.     [password] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
34.     [datetime] [datetime] NULL  DEFAULT (getdate()),
35.     [content] [ntext] COLLATE Chinese_PRC_CI_AS NULL
36. )
37. -----------------------------------------------------------------------------------
38. --插入数据
39. --SELECT COUNT(1) FROM userinfo
40. DECLARE @I int
41. SET @I=1
42. WHILE @I<=100 BEGIN --需要测试时间性能请修改此处
43.     INSERT INTO userinfo (
44.         [user],
45.         password,
46.         [datetime],
47.         [content]
48.     )
49.     VALUES (
50.         'user ' + CAST(@I AS nvarchar(50)),
51.         'password ' + CAST(@I AS nvarchar(50)),
52.         GETDATE(),
53.         '添加系统用户组添加系统用户组添加系统用户组添加系统用户组添加系统用户组添加系统用户组'
54.     )
55.     SET @I = @I + 1
56. END
57. ---------------------------此处的分页存储过程均不是通用的，通用的都要拼接Sql语句----------------------------
58. -- Description:利用Sql2005 ROW_NUMBER，分页存储过程(不需要主键，不需要拼接Sql)
59. CREATE PROCEDURE [dbo].[spPage2005]
60. (
61. @pageSize int = 20,    ----每页显示的记录个数
62. @pageIndex int = 1,    ----要显示那一页的记录
63. @Counts int = 0 OUTPUT      ----查询到的记录数
64. )
65. AS
66. SET NOCOUNT ON
67. DECLARE @pageUp INT 
68. DECLARE @pageDown INT
69. --获得总记录数
70. SELECT @Counts = COUNT(1) FROM UserInfo
71. --当前页的第一条记录RowID
72. SET @pageDown = @pageSize * (@pageIndex - 1) + 1
73. --当前页的最后一条记录RowID
74. SET @pageUp = @pageSize * @pageIndex
75. --获取分页后的数据
76. SELECT T.* --没有主键此处不用 T.
77. FROM 
78. (
79. SELECT id ,--没有主键此处改用 *
80.    RowID = ROW_NUMBER () OVER (ORDER BY id DESC)
81. FROM UserInfo
82. ) AS PageTableList
83. JOIN UserInfo AS T ON T.id = PageTableList.id --没有主键此句删除
84. WHERE RowID BETWEEN @pageDown AND @pageUp
85. ORDER BY T.id DESC --没有主键此句删除
86. --------------------------------------------------------------------
87. -- Description:Sql2000，分页存储过程 NOT IN(需要主键，需要拼接Sql)
88. CREATE PROCEDURE dbo.spPage2000
89. (
90.     @pageSize int = 20,            ----每页显示的记录个数
91.     @pageIndex int = 1,            ----要显示那一页的记录
92.     @Counts int = 0 OUTPUT      ----查询到的记录数
93. )
94. AS
95. SET NOCOUNT ON
96. DECLARE @pageUp INT 
97. DECLARE @SQL nvarchar(4000) 
98. --获得总记录数
99. SELECT @Counts = COUNT(1) FROM UserInfo
100. SET @pageUp = @pageSize * (@pageIndex - 1)
101. --获取分页后的数据
102. SET @SQL=
103. 'SELECT TOP ' + CAST(@pageSize AS varchar(20)) +
104. ' * FROM UserInfo ' +
105. ' WHERE id NOT IN '+
106. '('+
107. 'SELECT TOP ' + CAST(@pageUp AS varchar(20)) + ' id ' +
108. ' FROM UserInfo ORDER BY id DESC ' +
109. ') ORDER BY id DESC '
110. EXEC(@SQL)
111. -----------------------------------------------------------------------------------
112. -- Description:Sql2000，分页存储过程 三次 ORDER BY(不需要主键，需要拼接Sql)
113. CREATE PROCEDURE dbo.spPage2001
114. (
115.     @pageSize int = 20,            ----每页显示的记录个数
116.     @pageIndex int = 1,            ----要显示那一页的记录
117.     @Counts int = 0 OUTPUT      ----查询到的记录数
118. )
119. AS
120. SET NOCOUNT ON
121. DECLARE @pageUp INT 
122. DECLARE @SQL nvarchar(4000) 
123. --获得总记录数
124. SELECT @Counts = COUNT(1) FROM UserInfo
125. SET @pageUp = @pageSize * (@pageIndex + 1)
126. --获取分页后的数据
127. SET @SQL=
128. ('SELECT * FROM
129.         (SELECT TOP ' + CAST(@pageSize AS varchar(20)) + ' * FROM ' +
130.             '(SELECT TOP ' + CAST(@pageUp AS varchar(20)) + ' * FROM UserInfo   ORDER BY id DESC) TB2 '+
131.             ' ORDER BY id ASC ) TB3 '+
132.               ' ORDER BY id DESC  ')
133. EXEC(@SQL)
134. -----------------------------------------------------------------------------------
135. -- Description:Sql2000，分页存储过程 临时表(需要主键，不需要拼接Sql)
136. CREATE PROCEDURE dbo.spPage2002
137. (
138.     @pageSize int = 20,            ----每页显示的记录个数
139.     @pageIndex int = 1,            ----要显示那一页的记录
140.     @Counts int = 0 OUTPUT      ----查询到的记录数
141. )
142. AS
143. SET NOCOUNT ON
144. DECLARE @pageUp INT 
145. DECLARE @pageDown INT 
146. --获得总记录数
147. SELECT @Counts = COUNT(1) FROM UserInfo
148. --当前页的第一条记录RowID
149. SET @pageDown = @pageSize * (@pageIndex - 1) + 1
150. --当前页的最后一条记录RowID
151. SET @pageUp = @pageSize * @pageIndex
152. --定义插入临时表的总数据
153. SET rowcount @pageUp
154. --定义临时表变量
155. DECLARE @indextable table(indexID int identity(1,1),nid int)
156. --插入到临时表
157. INSERT INTO @indextable(nid) SELECT ID FROM UserInfo ORDER BY ID DESC
158. --获取分页后的数据
159. SELECT * FROM UserInfo p,@indextable t WHERE p.ID=t.nid
160. AND t.indexID>[email protected] and t.indexID<[email protected] ORDER BY t.indexID
161. -----------------------------------------------------------------------------------
162. -- Description:Sql2000，分页存储过程 游标(不需要主键，不需要拼接Sql)
163. --此存储过程 出来多一个空的结果集 ，暂时不明白
164. CREATE PROCEDURE dbo.spPage2003 
165. (  
166.     @sql nvarchar(4000) = NULL, --要执行的sql语句
167.     @pageSize int = 20,    ----每页显示的记录个数
168.     @pageIndex int = 1,    ----要显示那一页的记录
169.     @Counts int = 0 OUTPUT      ----查询到的记录数
170. )
171. AS
172. SET NOCOUNT ON
173. DECLARE @p1 int--P1是游标的id 
174. SET @sql = 'SELECT * FROM UserInfo Order By Id Desc'
175. EXEC sp_cursoropen @p1 OUTPUT,@sql,@scrollopt=1,@ccopt=1,@[email protected] OUTPUT
176. SELECT @Counts=ceiling(1.0*@Counts/@pageSize) ,@pageIndex=(@pageIndex-1)*@pageSize+1
177. --SELECT @pageIndex
178. EXEC sp_cursorfetch @p1,16,@pageIndex,@pageSize
179. --EXEC sp_cursorclose @p1