在SQL Server数据库操作中，我们常常会用到存储过程对实现对查询的数据的分页处理，以方便浏览者的浏览。本文我们总结了五种SQL Server分页存储过程的方法，并对其性能进行了比较，接下来就让我们来一起了解一下这一过程。
创建数据库data_Test ：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
create
database
data_Test
GO
use data_Test
GO
create
table
tb_TestTable
--创建表
(
id
int
identity(1,1)
primary
key
,
userName nvarchar(20)
not
null
,
userPWD nvarchar(20)
not
null
,
userEmail nvarchar(40)
null
)
GO
|
插入数据：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
set
identity_insert tb_TestTable
on
declare
@
count
int
set
@
count
=1
while @
count
<=2000000
begin
insert
into
tb_TestTable(id,userName,userPWD,userEmail)
values
(@
count
,
'admin'
,
'admin888'
,
'lli0077@yahoo.com.cn'
)
set
@
count
=@
count
+1
end
set
identity_insert tb_TestTable
off
|
1、利用select top 和select not in进行分页
具体代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
create
procedure
proc_paged_with_notin
--利用select top and select not in
(
@pageIndex
int
,
--页索引
@pageSize
int
--每页记录数
)
as
begin
set
nocount
on
;
declare
@timediff datetime
--耗时
declare
@sql nvarchar(500)
select
@timediff=Getdate()
set
@sql=
'select top '
+str(@pageSize)+
' * from tb_TestTable where(ID not in(select top '
+str(@pageSize*@pageIndex)+
' id from tb_TestTable order by ID ASC)) order by ID'
execute
(@sql)
--因select top后不支技直接接参数，所以写成了字符串@sql
select
datediff(ms,@timediff,GetDate())
as
耗时
set
nocount
off
;
end
|
2、利用select top 和 select max(列键)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
create
procedure
proc_paged_with_selectMax
--利用select top and select max(列)
(
@pageIndex
int
,
--页索引
@pageSize
int
--页记录数
)
as
begin
set
nocount
on
;
declare
@timediff datetime
declare
@sql nvarchar(500)
select
@timediff=Getdate()
set
@sql=
'select top '
+str(@pageSize)+
' * From tb_TestTable where(ID>(select max(id) From (select top '
+str(@pageSize*@pageIndex)+
' id From tb_TestTable order by ID) as TempTable)) order by ID'
execute
(@sql)
select
datediff(ms,@timediff,GetDate())
as
耗时
set
nocount
off
;
end
|
ALTER procedure proc_paged_with_selectMax --利用select top and select max(列)
(
@pageIndex int, --页索引
@pageSize int --页记录数
)
as
begin
set nocount on;
declare @timediff datetime
declare @sql nvarchar(500)
declare @maxid int
declare @total int
set @total=@pageSize*@pageIndex
select @timediff=Getdate()
select @maxid=max(id) From (select top(@total) id From tb_TestTable order by ID) as TempTable
if @maxid is null
begin
set @maxid=0
end
set @sql='select top '+str(@pageSize)+' * From tb_TestTable where(ID>('+str(@maxid)+')) order by ID'
execute(@sql)
select datediff(ms,@timediff,GetDate()) as 耗时
set nocount off;
endcreate procedure proc_paged_with_selectMax2 --利用select top and select max(列)
(
@pageIndex int, --页索引
@pageSize int --页记录数
)
as
begin
set nocount on;
declare @timediff datetime
declare @sql nvarchar(500)
select @timediff=Getdate()
--case when @count<=@pageSize*@pageIndex then ID else @ID end
set @sql='select top '+str(@pageSize)+' * From tb_TestTable where(ID>(select case when max(id) is not null then max(id) else 0 end From (select top '+str(@pageSize*@pageIndex)+' id From tb_TestTable order by ID) as TempTable)) order by ID'
execute(@sql)
select datediff(ms,@timediff,GetDate()) as 耗时
set nocount off;
end
3、利用select top和中间变量
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
create
procedure
proc_paged_with_Midvar
--利用ID>最大ID值和中间变量
(
@pageIndex
int
,
@pageSize
int
)
as
declare
@
count
int
declare
@ID
int
declare
@timediff datetime
declare
@sql nvarchar(500)
begin
set
nocount
on
;
select
@
count
=0,@ID=0,@timediff=getdate()
select
@
count
=@
count
+1,@ID=
case
when
@
count
<=@pageSize*@pageIndex
then
ID
else
@ID
end
from
tb_testTable
order
by
id
set
@sql=
'select top '
+str(@pageSize)+
' * from tb_testTable where ID>'
+str(@ID)
execute
(@sql)
select
datediff(ms,@timediff,getdate())
as
耗时
set
nocount
off
;
end
|
4、利用Row_number() 此方法为SQL server 2005中新的方法,利用Row_number()给数据行加上索引
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
create
procedure
proc_paged_with_Rownumber
--利用SQL 2005中的Row_number()
(
@pageIndex
int
,
@pageSize
int
)
as
declare
@timediff datetime
begin
set
nocount
on
;
select
@timediff=getdate()
select
*
from
(
select
*,Row_number() over(
order
by
ID
asc
)
as
IDRank
from
tb_testTable)
as
IDWithRowNumber
where
IDRank>@pageSize*@pageIndex
and
IDRank<@pageSize*(@pageIndex+1)
select
datediff(ms,@timediff,getdate())
as
耗时
set
nocount
off
;
end
|
alter procedure proc_paged_with_Rownumber --利用SQL 2005中的Row_number()
(
@pageIndex int,
@pageSize int
)
as
declare @timediff datetime
begin
set nocount on;
select @timediff=getdate()
select * from (select *,Row_number() over(order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank>@pageSize*@pageIndex and IDRank<=@pageSize*(@pageIndex+1)
select datediff(ms,@timediff,getdate()) as 耗时
set nocount off;
end
5、利用临时表及Row_number
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
create
procedure
proc_CTE
--利用临时表及Row_number
(
@pageIndex
int
,
--页索引
@pageSize
int
--页记录数
)
as
set
nocount
on
;
declare
@ctestr nvarchar(400)
declare
@strSql nvarchar(400)
declare
@datediff datetime
begin
select
@datediff=GetDate()
set
@ctestr=
'with Table_CTE as
(select ceiling((Row_number() over(order by ID ASC))/'
+str(@pageSize)+
') as page_num,* from tb_TestTable)'
;
set
@strSql=@ctestr+
' select * From Table_CTE where page_num='
+str(@pageIndex)
end
begin
execute
sp_executesql @strSql
select
datediff(ms,@datediff,GetDate())
set
nocount
off
;
end
|
以上的五种方法中，网上说第三种利用select top和中间变量的方法是效率最高的。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
