程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> sql server平台用存儲過程進行分頁的兩種方法

sql server平台用存儲過程進行分頁的兩種方法

編輯:關於SqlServer

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved