1、减少服务器磁盘系统地读取压力
2、减少网络流量，减轻网络压力
3、减轻客户端显示数据的压力
4、提高处理效率。
一般而言，分页处理分为两种：应用程序中的分页处理和数据库中的分页处理。目前大多数的应用都是在应用程序中借助支持数据分页处理的数据库访问组 件（如DataGrid控件）实现分页处理。实际上，在数据库中实现分页处理，可以从源头减少数据处理量，效果往往可能跟明显。本文主要讨论数据库的分页 问题。
常规的取第n页数据方法为：
1
Select
top
PageSize
*
2
from
TableA
3
where
Primary_Key
not
in
(
select
top
(n
-
1
)
*
PageSize Primary_Key
from
TableA )
Select
top
PageSize
*
2
from
TableA 3
where
Primary_Key
not
in
(
select
top
(n
-
1
)
*
PageSize Primary_Key
from
TableA )
对于应用程序而言，所做的就是在生成分页处理的T-SQL语句前先计算好各数字， 对于数据库而言，应该采用动态的T-SQL语句。
以下是使用上述原理实现的通用分页处理存储过程：
1
create
proc
up_PageView
2(
3
@tableName
sysname,
4
@colKey
nvarchar
(
100
),
5
@pageCurrent
int
=
1
,
6
@pageSize
int
=
10
,
7
@colShow
nvarchar
(
4000
)
=
''
,
8
@colOrder
nvarchar
(
200
)
=
''
,
9
@where
nvarchar
(
2000
)
=
''
,
10
@pageCount
int
output
11)
12
as
13
begin
14
if
object_id
(
@tableName
)
is
null
15
begin
16
raiserror
(
'
the table is not existing!
'
,
16
,
1
)
17
return
18
end
19
if
isnull
(
@colShow
,
''
)
=
''
20
set
@colShow
=
'
*
'
21
if
isnull
(
@colOrder
,
''
)
=
''
22
set
@colOrder
=
''
23
else
24
set
@colOrder
=
'
order by
'
+
@colOrder
25
if
isnull
(
@where
,
''
)
=
''
26
set
@where
=
''
27
else
28
set
@where
=
'
where
'
+
@where
29
declare
@sql
nvarchar
(
4000
)
30
if
@pageCount
is
null
31
begin
32
set
@sql
=
'
select @pageCount = count(*) from
'
+
@tableName
+
'
'
+
@where
33
Exec
sp_executesql
@sql
,
'
@pageCount int output
'
,
@pageCount
output
34
set
@pageCount
=
(
@pageCount
+
@pageSize
-
1
)
/
@pageSize
35
end
36
if
@pageCurrent
=
1
37
set
@sql
=
'
select top
'
+
'
'
+
convert
(
nvarchar
(
10
),
@pageSize
)
+
'
'
38
+
@colshow
+
'
'
+
'
from
'
+
@tableName
+
'
'
+
@where
+
'
'
+
@colOrder
39
else
40
begin
41
set
@sql
=
'
select top
'
+
'
'
+
convert
(
nvarchar
(
10
),
@pageSize
)
+
'
'
42
+
@colshow
+
'
'
+
'
from
'
+
@tableName
+
'
'
+
@where
43
set
@sql
=
@sql
+
'
'
+
'
and
'
+
@colKey
+
'
not in (
'
44
+
'
select top
'
+
'
'
+
convert
(
nvarchar
(
10
), (
@pageCurrent
-
1
)
*
@pageSize
)
+
'
'
45
+
@colKey
+
'
'
+
'
from
'
+
@tableName
+
'
'
+
@where
+
'
)
'
46
set
@sql
=
@sql
+
'
'
+
@colOrder
47
end
48
--
execute the dynamic query
49
50
exec
(
@sql
)
51
end
52
53
create
proc
up_PageView2
(3
@tableName
sysname,4
@colKey
nvarchar
(
100
),5
@pageCurrent
int
=
1
,6
@pageSize
int
=
10
,7
@colShow
nvarchar
(
4000
)
=
''
,8
@colOrder
nvarchar
(
200
)
=
''
,9
@where
nvarchar
(
2000
)
=
''
,10
@pageCount
int
output11
)12
as
13
begin
14
if
object_id
(
@tableName
)
is
null
15
begin
16
raiserror
(
'
the table is not existing!
'
,
16
,
1
)17
return
18
end
19
if
isnull
(
@colShow
,
''
)
=
''
20
set
@colShow
=
'
*
'
21
if
isnull
(
@colOrder
,
''
)
=
''
22
set
@colOrder
=
''
23
else
24
set
@colOrder
=
'
order by
'
+
@colOrder
25
if
isnull
(
@where
,
''
)
=
''
26
set
@where
=
''
27
else
28
set
@where
=
'
where
'
+
@where
29
declare
@sql
nvarchar
(
4000
)30
if
@pageCount
is
null
31
begin
32
set
@sql
=
'
select @pageCount = count(*) from
'
+
@tableName
+
'
'
+
@where
33
Exec
sp_executesql
@sql
,
'
@pageCount int output
'
,
@pageCount
output34
set
@pageCount
=
(
@pageCount
+
@pageSize
-
1
)
/
@pageSize
35
end
36
if
@pageCurrent
=
1
37
set
@sql
=
'
select top
'
+
'
'
+
convert
(
nvarchar
(
10
),
@pageSize
)
+
'
'
38
+
@colshow
+
'
'
+
'
from
'
+
@tableName
+
'
'
+
@where
+
'
'
+
@colOrder
39
else
40
begin
41
set
@sql
=
'
select top
'
+
'
'
+
convert
(
nvarchar
(
10
),
@pageSize
)
+
'
'
42
+
@colshow
+
'
'
+
'
from
'
+
@tableName
+
'
'
+
@where
43
set
@sql
=
@sql
+
'
'
+
'
and
'
+
@colKey
+
'
not in (
'
44
+
'
select top
'
+
'
'
+
convert
(
nvarchar
(
10
), (
@pageCurrent
-
1
)
*
@pageSize
)
+
'
'
45
+
@colKey
+
'
'
+
'
from
'
+
@tableName
+
'
'
+
@where
+
'
)
'
46
set
@sql
=
@sql
+
'
'
+
@colOrder
47
end
48
--
execute the dynamic query
49
50
exec
(
@sql
)51
end
52
53
这种方法的缺点是为了排除该页以前的页， 必须使用top n取大量的数据并缓存起来，在关联元表查询出最终结果，这样做的效率比较低。通常情况 下， 我们都是对单主健（使用单个字段定位纪录）的表进行分页查询。因此，如果能使用一个字符串变量纪录指定页的所有主健，在使用in子句配合纪录的指定 页主健就可以查询出最终的结果来。下面是改进的存储过程：
1
create
proc
up_PageView
2(
3
@tableName
sysname,
4
@colKey
nvarchar
(
100
),
5
@pageCurrent
int
=
1
,
6
@pageSize
int
=
10
,
7
@colShow
nvarchar
(
4000
)
=
''
,
8
@colOrder
nvarchar
(
200
)
=
''
,
9
@where
nvarchar
(
2000
)
=
''
,
10
@pageCount
int
output
11)
12
as
13
begin
14
if
object_id
(
@tableName
)
is
null
15
begin
16
raiserror
(
'
the table is not existing!
'
,
16
,
1
)
17
return
18
end
19
if
isnull
(
@colShow
,
''
)
=
''
20
set
@colShow
=
'
*
'
21
if
isnull
(
@colOrder
,
''
)
=
''
22
set
@colOrder
=
''
23
else
24
set
@colOrder
=
'
order by
'
+
@colOrder
25
if
isnull
(
@where
,
''
)
=
''
26
set
@where
=
''
27
else
28
set
@where
=
'
where
'
+
@where
29
declare
@sql
nvarchar
(
4000
)
30
if
@pageCount
is
null
31
begin
32
set
@sql
=
'
select @pageCount = count(*) from
'
+
@tableName
+
'
'
+
@where
33
Exec
sp_executesql
@sql
,
'
@pageCount int output
'
,
@pageCount
output
34
set
@pageCount
=
(
@pageCount
+
@pageSize
-
1
)
/
@pageSize
--
get total pages
35
36
end
37
if
@pageCurrent
=
1
38
begin
39
set
@sql
=
N
'
select top
'
+
N
'
'
+
convert
(
nvarchar
(
10
),
@pageSize
)
+
N
'
'
40
+
@colshow
+
N
'
'
+
N
'
from
'
+
@tableName
+
N
'
'
+
@where
+
N
'
'
+
@colOrder
41
exec
(
@sql
)
42
end
43
else
44
begin
45
declare
@topN
int
,
@topN1
int
46
--
set @topN = @pageSize
47
48
--
set @topN1 = @pageCurrent * @pageSize
49
50
set
@pageCurrent
=
@pageCurrent
*
@pageSize
51
set
@sql
=
N
'
select @n = @n - 1, @s = case when @n <
'
+
convert
(
nvarchar
(
10
),
@pageSize
)
+
52 N
'
then @s +
''
,
''
+ quotename(@colKey, N
''''''''
)
'
+
N
'
else
'''''
+
N
'
end
'
53
+
N
'
from
'
+
@tableName
+
N
'
'
+
@where
54
--
make query effect only @pageCurrent records
55
56
--
Query only top @pageCurrent * @pageSize
57
58
set
rowcount
@pageCurrent
59
exec
sp_executesql
@sql
,
'
@n int, @s nvarchar(max) output
'
,
@pageCurrent
,
@sql
output
60
set
rowcount
0
--
recover to default config
61
62
set
@sql
=
stuff
(
@sql
,
1
,
1
, N
''
)
--
remove the first ','
63
64
--
exec the query
65
66
Exec
(N
'
select
'
+
@colShow
+
N
'
'
+
'
from
'
+
N
'
'
+
@tableName
+
N
'
'
67
+
N
'
where
'
+
@colKey
+
N
'
in (
'
+
@sql
+
'
)
'
+
@colOrder
)
68
end
69
end
70
go
create
proc
up_PageView2
(3
@tableName
sysname,4
@colKey
nvarchar
(
100
),5
@pageCurrent
int
=
1
,6
@pageSize
int
=
10
,7
@colShow
nvarchar
(
4000
)
=
''
,8
@colOrder
nvarchar
(
200
)
=
''
,9
@where
nvarchar
(
2000
)
=
''
,10
@pageCount
int
output11
)12
as
13
begin
14
if
object_id
(
@tableName
)
is
null
15
begin
16
raiserror
(
'
the table is not existing!
'
,
16
,
1
)17
return
18
end
19
if
isnull
(
@colShow
,
''
)
=
''
20
set
@colShow
=
'
*
'
21
if
isnull
(
@colOrder
,
''
)
=
''
22
set
@colOrder
=
''
23
else
24
set
@colOrder
=
'
order by
'
+
@colOrder
25
if
isnull
(
@where
,
''
)
=
''
26
set
@where
=
''
27
else
28
set
@where
=
'
where
'
+
@where
29
declare
@sql
nvarchar
(
4000
)30
if
@pageCount
is
null
31
begin
32
set
@sql
=
'
select @pageCount = count(*) from
'
+
@tableName
+
'
'
+
@where
33
Exec
sp_executesql
@sql
,
'
@pageCount int output
'
,
@pageCount
output34
set
@pageCount
=
(
@pageCount
+
@pageSize
-
1
)
/
@pageSize
--
get total pages
35
36
end
37
if
@pageCurrent
=
1
38
begin
39
set
@sql
=
N
'
select top
'
+
N
'
'
+
convert
(
nvarchar
(
10
),
@pageSize
)
+
N
'
'
40
+
@colshow
+
N
'
'
+
N
'
from
'
+
@tableName
+
N
'
'
+
@where
+
N
'
'
+
@colOrder
41
exec
(
@sql
)42
end
43
else
44
begin
45
declare
@topN
int
,
@topN1
int
46
--
set @topN = @pageSize
47
48
--
set @topN1 = @pageCurrent * @pageSize
49
50
set
@pageCurrent
=
@pageCurrent
*
@pageSize
51
set
@sql
=
N
'
select @n = @n - 1, @s = case when @n <
'
+
convert
(
nvarchar
(
10
),
@pageSize
)
+
52
N
'
then @s +
''
,
''
+ quotename(@colKey, N
''''''''
)
'
+
N
'
else
'''''
+
N
'
end
'
53
+
N
'
from
'
+
@tableName
+
N
'
'
+
@where
54
--
make query effect only @pageCurrent records
55
56
--
Query only top @pageCurrent * @pageSize
57
58
set
rowcount
@pageCurrent
59
exec
sp_executesql
@sql
,
'
@n int, @s nvarchar(max) output
'
,
@pageCurrent
,
@sql
output60
set
rowcount
0
--
recover to default config
61
62
set
@sql
=
stuff
(
@sql
,
1
,
1
, N
''
)
--
remove the first ','
63
64
--
exec the query
65
66
Exec
(N
'
select
'
+
@colShow
+
N
'
'
+
'
from
'
+
N
'
'
+
@tableName
+
N
'
'
67
+
N
'
where
'
+
@colKey
+
N
'
in (
'
+
@sql
+
'
)
'
+
@colOrder
)68
end
69
end
70
go
另外， sql server 2005 增加了一些新的功能如取得排名或顺序的函数 （Rank(), Dense_Rank(), Row_Number()）, 利用这些新的功能也能进行分页处理，下面以 sql server 2005 自带的数据库AdventureWorks为例结合Row_Number() 实现分页处理：
1
create
proc
up_GetPagen
2(
3
@pageSize
int
,
4
@pageCurrent
int
5)
6
as
7
begin
8
select
*
from
9 (
select
ROW_NUMBER()
over
(
ORDER
BY
productid) RowNum,
*
from
production.product )OrderData
10
where
RowNum
between
(
@pageCurrent
-
1
)
*
@pageSize
+
1
and
@pageCurrent
*
@pageSize
11
order
by
productid
12
end
create
proc
up_GetPagen2
(3
@pageSize
int
, 4
@pageCurrent
int
5
)6
as
7
begin
8
select
*
from
9
(
select
ROW_NUMBER()
over
(
ORDER
BY
productid) RowNum,
*
from
production.product )OrderData10
where
RowNum
between
(
@pageCurrent
-
1
)
*
@pageSize
+
1
and
@pageCurrent
*
@pageSize
11
order
by
productid12
end
不尽如此，这种新功能有许多妙用， 如可以取班级排名前N名或第n名到第m名的学生等等，这类问题将会在我以后的文章中进行讨论！
6728
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
