1

SQL Server 存储过程的分页，这个问题已经讨论过几年了，很多朋友在问我，所以在此发表一下我的观点

  2

建立表：

  3

  4

CREATE

 

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

在实际情况中，要具体分析。