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

killergo的专栏

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

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

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

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

CREATE proc sp_fixpage @pagesize int,@destpage int; as set nocount ondeclare @id intdeclare @startid int

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

set rowcount @startidselect @id = id from t_member

set rowcount @pagesizeset nocount offselect * from t_member where id > @id order by idGO

第二种:

CREATE PROCEDURE sp_fixpage1 @pagesize int ,@destpage intas 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 = 1set @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 myCursorfetch 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 @nowIDend

close myCursordeallocate myCursor

set nocount off select * from #myTabledrop table #myTableGO