Comparison of paging solutions for SQL Server Stored Procedures [Reprinted from Li honggen blogs]

The paging of the SQL Server Stored Procedure has been discussed for several years. Many friends are asking me, so I would like to express my point of view here.
Create a table:

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

 

Insert data: (20 thousand, more data will be used for testing)
Set identity_insert testtable on

Declare @ I int

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

 

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

Paging solution 1: (use not in and select top pages)
Statement format:
Select top 10 *
From testtable
Where (id not in
(Select top 20 ID
From testtable
Order by ID ))
Order by ID

Select top page size *
From testtable
Where (id not in
(Select top page size * Page ID
From table
Order by ID ))
Order by ID

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

Paging solution 2: (use the ID greater than the number and select top pages)
Statement format:
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

Other solutions: If there is no primary key, you can use a temporary table or solution 3, but the efficiency is low.
We recommend that you add primary keys and indexes during optimization to improve query efficiency.

The SQL query Analyzer displays a comparison: My conclusion is:
Paging solution 2: (using more than ID and select top pages) is the most efficient. You need to splice an SQL statement
Paging solution 1: (using not in and select top pages) The efficiency is second, and SQL statements need to be spliced.
Paging solution 3: (using SQL cursor Stored Procedure paging) The efficiency is the worst, but the most common

In actual situations, specific analysis is required.

For more information, see:
http://community.csdn.net/Expert/topic/3292/3292678.xml? Temp =. 1621515