1 SQLServer存儲(chǔ)過程的分頁，這個(gè)問題已經(jīng)討論過幾年了，很多朋友在問我，所以在此發(fā)表一下我的觀點(diǎn)
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 插入數(shù)據(jù)：(2萬條，用更多的數(shù)據(jù)測(cè)試會(huì)明顯一些)
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 頁大小 * 頁數(shù)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 頁大小 * 頁數(shù)id
70 FROM 表
71 ORDER BY id) AS T))
72 ORDER BY ID
73
74
75 -- -----------------------------------
76
77 分頁方案三：(利用SQL的游標(biāo)存儲(chǔ)過程分頁)
78 create procedure XiaoZhengGe
79 @sqlstr nvarchar ( 4000 ), -- 查詢字符串
80 @currentpage int , -- 第N頁
81 @pagesize int -- 每頁行數(shù)
82 as
83 set nocount on
84 declare @P1 int , -- P1是游標(biāo)的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 總頁數(shù) -- ,@rowcountas總行數(shù),@currentpageas當(dāng)前頁
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 其它的方案：如果沒有主鍵，可以用臨時(shí)表，也可以用方案三做，但是效率會(huì)低。
94 建議優(yōu)化的時(shí)候，加上主鍵和索引，查詢效率會(huì)提高。
95
96 通過SQL查詢分析器，顯示比較：我的結(jié)論是:
97 分頁方案二：(利用ID大于多少和SELECTTOP分頁）效率最高，需要拼接SQL語句
98 分頁方案一：(利用NotIn和SELECTTOP分頁)效率次之，需要拼接SQL語句
99 分頁方案三：(利用SQL的游標(biāo)存儲(chǔ)過程分頁)效率最差，但是最為通用
100
101 在實(shí)際情況中，要具體分析。