Common SQL paging algorithms and comparisons

New methods introduced by SQL Server 2005.

1 SELECT *  from(SELECTRow_number () Over(ORDER  byKeyfieldDESC) asRowNum,*  fromTableName) asTWHERERowNum>Start[For example:]  andRowNum<= End[For example:]=[back to 91-100]2 3 SELECT Top(PAGESIZE[For example: Ten]) from(SELECTRow_number () Over(ORDER  byKeyfieldDESC) asRowNum,*  fromTableName) asTWHERERowNum>(PAGEINDEX-1)*(PAGESIZE)[For example:]4  =[back to 91-100]

which
Keyfield is a field of table TableName (preferably a primary key);
TableName is the name of the table queried;
Desc can be changed to ASC on demand;
Start is the starting record for the result set to be taken
End is the end record of the result set to be taken, which can be computed by: (Start + pageSize).

General method: The primary key in the table must be an identity column, [ID] int identity (+)
Create a table

1 CREATE TABLE [testtable]2     (3       [ID] [int] IDENTITY(1,1)4                  PRIMARY KEY5                   not NULL ,6       [FirstName] [nvarchar]( -)NULL ,7       [LastName] [nvarchar]( -)NULL ,8       [Country] [nvarchar]( -)NULL ,9       [Note] [nvarchar]( -)NULLTen     ) One GO

Insert data: (20,000, test with more data will be obvious)

SET Identity_insertTestTable onDECLARE @i INTSET @i = 1 while @i <= 20000     BEGIN        INSERT   intoTestTable ([ID], FirstName, LastName, Country, Note )        VALUES(@i ,                  'firstname_xxx' ,                  'lastname_xxx' ,                  'country_xxx' ,                  'note_xxx'                )        SET @i = @i + 1    ENDSET Identity_insertTestTableOFF

1. Paging Scenario One: (using not in and select top paging)

Statement form:

SELECT TOP Ten *  fromtesttableWHERE(ID not inch           (SELECT TOP  -ID fromtesttableORDER  byID))ORDER  byIDSELECT TOPPage size* fromTableWHERE(ID not inch           (SELECT TOPPage size*page ID fromTableORDER  byID))ORDER  byId

2. Paging Scheme II: (using ID greater than how much and select top paging)

Statement form:

SELECT TOP Ten *  fromtesttableWHERE(ID>           (SELECT MAX(ID) from(SELECT TOP  -ID fromtesttableORDER  byId asT))ORDER  byIDSELECT TOPPage size* fromTableWHERE(ID>           (SELECT MAX(ID) from(SELECT TOPPage size*page ID fromTableORDER  byId asT))ORDER  byID

3. Paging Scenario Three: (Paging with SQL cursor stored procedures)

CREATE  PROCEDURESqlPager@sqlstr NVARCHAR(4000) ,--query string    @currentpage INT,--Page N    @pagesize INT --number of rows per page as     SETNocount on    DECLARE @P1 INT,--P1 is the ID of the cursor        @rowcount INT    EXECSp_cursoropen@P1OUTPUT,@sqlstr,@scrollopt = 1,@ccopt = 1,        @rowcount = @rowcountOUTPUTSELECT  CEILING(1.0 * @rowcount / @pagesize) asTotal pages--, @rowcount as total number of rows, @currentpage as current page    SET @currentpage =(@currentpage - 1)* @pagesize + 1    EXECSp_cursorfetch@P1, -,@currentpage,@pagesize     EXECSp_cursorclose@P1    SETNocountOFF

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 tuning is recommended, the query efficiency increases with the primary key and index.


paging Scenario two: (using IDs greater than how much and select top paging) is most efficient and requires stitching SQL statements
paging Scenario One: (using not in and select top paging)     Efficiency second, need to splice SQL statement