sql server平台用存储过程进行分页的两种方法

killergo的专栏

最近因为稍微有点空闲时间，所以想了下在sql server平台用存储过程的分页方式，现在列示在下面。

实际测试时，在15000条数据情况下两者性能大体相当，在20000-30000条数据的情况下前者明显比后者性能更佳。更大数据量没有进行测试了。

注意，数据表里面是否有 键和索引 对性能的影响相当大
-----------------------------------------------------
第一种:

/*第一个参数是每页条数，第二个参数是目标页码*/

create proc sp_fixpage @pagesize int,@destpage int  as
set nocount on
declare @id int
declare @startid int

select @startid = (@destpage - 1)*@pagesize

set rowcount @startid
select @id = id from t_member

set rowcount @pagesize
set nocount off
select * from t_member where id > @id order by id
go

第二种:

create procedure sp_fixpage1 @pagesize int ,@destpage int
as
set nocount on
 
create table #mytable(
 [id] [int] not null ,
 [username] [varchar] (50) collate chinese_prc_ci_as not null ,
 [name] [varchar] (50) collate chinese_prc_ci_as null ,
 [origin] [int] null ,
 [latencybuydegree] [varchar] (50) collate chinese_prc_ci_as null ,
 [usertype] [varchar] (2) collate chinese_prc_ci_as null ,
 [email] [varchar] (50) collate chinese_prc_ci_as null ,
 [userlev] [int] null ,
 [regtime] [datetime] null ,
 [regmode] [bit] null ,
 [papernum] [varchar] (50) collate chinese_prc_ci_as null ,
 [userclass] [bit] null ,
 [password] [binary] (64) null ,
 [tel] [varchar] (50) collate chinese_prc_ci_as null ,
 [drass] [varchar] (150) collate chinese_prc_ci_as null ,
 [zip] [varchar] (50) collate chinese_prc_ci_as null ,
 [papernumlb] [int] null ,
 [opuser] [varchar] (50) collate chinese_prc_ci_as null ,
 [province] [varchar] (50) collate chinese_prc_ci_as null ,
 [birthdate] [datetime] null
) on [primary]

declare @temppos int
declare @abspos int
declare @nowid int

set @temppos = 1
set @abspos = 1

if @destpage > 1
    set @abspos = (@pagesize*(@destpage- 1) + 1)

   declare mycursor scroll cursor for
      select [id] from t_member order by id

open mycursor
fetch absolute @abspos from mycursor into @nowid

while (@@fetch_status = 0) and (@temppos <= @pagesize)
begin
  set @temppos = @temppos + 1
  insert into #mytable select * from t_member where [id] = @nowid
  fetch next from mycursor into @nowid
end

close mycursor
deallocate mycursor

set nocount off
 
select * from #mytable
drop table #mytable
go