SQL Server paging solution Competition

SQLServer

Create Table [testtable] (
[ID] [int] identity (1, 1) not null,
[Firstname] [nvarchar] (100) Collate chinese_prc_ci_as null,
[Lastname] [nvarchar] (100) Collate chinese_prc_ci_as null,
[Country] [nvarchar] (50) Collate chinese_prc_ci_as null,
[Note] [nvarchar] (2000) Collate chinese_prc_ci_as null
) On [primary]
Go

 
--------------------------------------
Set identity_insert testtable on

Declare @ I int
Set @ I = 1
While I <= 20000
Begin
Insert into testtable ([ID], firstname, lastname, country, note) values (@ I, 'firstname _ XXX', 'lastname _ XXX', 'country _ XXX ', 'note _ XXX ')
Set @ I = @ I + 1
End

Set identity_insert testtable off

 

-------------------------------------

 
Select top 10 *
From testtable
Where (id not in
(Select top 20 ID
From testtable
Order by ID ))
Order by ID

Select top secondary nodes *
From testtable
Where (id not in
(Select top secondary with primary ID
From table
Order by ID ))
Order by ID

-------------------------------------

 
Select top 10 *
From testtable
Where (ID>
(Select max (ID)
From (select top 20 ID
From testtable
Order by ID) as t ))
Order by ID

Select top page size *
From testtable
Where (ID>
(Select max (ID)
From (select top page size * Page ID
From table
Order by ID) as t ))
Order by ID

-------------------------------------

Paging solution 3: (using SQL cursor Stored Procedure paging)
Create procedure xiaozhengge
@ Sqlstr nvarchar (4000), -- query string
@ Currentpage int, -- page n
@ Pagesize int -- number of lines per page
As
Set nocount on
Declare @ P1 int, -- P1 is the cursor ID
@ Rowcount int
Exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
Select ceiling (1.0 * @ rowcount/@ pagesize) as total number of pages --, @ rowcount as total number of rows, @ currentpage as current page
Set @ currentpage = (@ currentpage-1) * @ pagesize + 1
Exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize
Exec sp_cursorclose @ p1
Set nocount off