Two methods of paging with stored procedures in SQL Server platform

server| Stored Procedures | paging

Killergo's Column

Recently, because of a bit of free time, I thought about how to use the paging of stored procedures on the SQL Server platform, now listed below.

In the actual test, the performance of the two is roughly equal in the case of 15,000 data, and the former is obviously better than the latter in the case of 20000-30000 data. The larger amount of data has not been tested.

Note that there are keys and indexes in the datasheet that have a significant impact on performance
-----------------------------------------------------
First type:

/* The first parameter is the number of pages per page, the second parameter is the target page number * *

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

The second type:

CREATE PROCEDURE sp_fixpage1 @pagesize int, @destpage int
As
SET NOCOUNT ON

CREATE TABLE #myTable (
[ID] [int] not NULL,
[UserName] [varchar] (m) COLLATE chinese_prc_ci_as not NULL,
[Name] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
[Origin] [INT] Null
[Latencybuydegree] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
[Usertype] [varchar] (2) COLLATE chinese_prc_ci_as NULL,
[Email] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
[Userlev] [INT] Null
[Regtime] [DateTime] Null
[Regmode] [Bit] Null
[Papernum] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
[UserClass] [Bit] Null
[Password] [Binary] (a) NULL,
[Tel] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
[Drass] [varchar] (COLLATE) Chinese_prc_ci_as NULL,
[Zip] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
[PAPERNUMLB] [INT] Null
[Opuser] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
[Province] [varchar] (m) 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 to @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