Three kinds of paging methods and comparison _mssql2008 in SQL SERVER 2008

Create a table:

Copy Code code as follows:

CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) not NULL,
[FirstName] [nvarchar] (MB) COLLATE chinese_prc_ci_as NULL,
[LastName] [nvarchar] (MB) COLLATE chinese_prc_ci_as NULL,
[Country] [nvarchar] (m) COLLATE chinese_prc_ci_as NULL,
[Note] [nvarchar] (Watts) COLLATE chinese_prc_ci_as NULL
) on [PRIMARY]
Go

Insert data: (20,000, test with more data will be obvious)
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

Paging Scheme one: (using not in and select top pagination)

Statement form:

Copy Code code as follows:

SELECT Top 10 *
From TestTable
WHERE (ID not in
(SELECT Top ID
From TestTable
Order by ID)
ORDER BY ID

SELECT Top Page Size *
From TestTable
WHERE (ID not in
(SELECT top Page size * Pages ID
From table
Order by ID)
ORDER BY ID

Paging Scheme two: (with ID greater than how much and select top pagination)
Statement form:

Copy Code code as follows:

SELECT Top *
from testtable
WHERE (Id >
           (SELECT MAX (ID)
         from ( SELECT Top ID
                  from TestTable
                  order by ID) as (T))
Order by ID

SELECT top Page Size *
from testtable
WHERE (ID
&N bsp;         (SELECT MAX (ID)
          from (SELECT top page size * pages ID
                  from table
                  order by ID) as (T))
Order by ID

Paging Scenario Three: (using SQL Cursor stored procedure paging)

Copy Code code as follows:

CREATE PROCEDURE Xiaozhengge
@sqlstr nvarchar (4000),--query string
@currentpage int,--nth page
@pagesize INT--Number of rows per page
As
SET NOCOUNT ON
declare @P1 int,--P1 is the ID of the cursor
@rowcount int
EXEC sp_cursoropen @P1 output, @sqlstr, @scrollopt =1, @ccopt =1, @rowcount = @rowcount output
Select Ceiling (1.0* @rowcount/@pagesize) as total number of pages--, @rowcount as rows, @currentpage as current page
Set @currentpage = (@currentpage-1) * @pagesize +1
exec sp_cursorfetch @P1, @currentpage, @pagesize
EXEC sp_cursorclose @P1
SET NOCOUNT OFF

Other scenarios: If you don't have a primary key, you can use a temporary table, or you can do it with scenario three, but it's inefficient.
When optimization is recommended, the query efficiency is increased by adding primary keys and indexes.

Displays comparisons through SQL Query Analyzer: My conclusion is:
Paging scheme two: (with ID greater than the number and select top paging) The most efficient, need to stitch SQL statements
Paging scheme one: (with not in and select top pagination)    efficiency Next, need to splice SQL statements
Paging scheme three: (using SQL Cursor stored procedure paging)     inefficient, but most common
in the actual situation, to be specific analysis.