Three pagination methods and summaries in SQL SERVER 2008

Create a table:

1 CREATE TABLE [testtable] (2  [ID] [int] IDENTITY(1,1) not NULL ,3  [FirstName] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL ,4  [LastName] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL ,5  [Country] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL ,6  [Note] [nvarchar]( -) COLLATE Chinese_prc_ci_asNULL7) on [PRIMARY]8 GO9 Ten Insert Data: (20,000, test with more data will be obvious) One SET Identity_insertTestTable on A Declare @i int - Set @i=1 -  while @i<=20000 the begin -     Insert  intoTestTable ([ID], FirstName, LastName, Country,note)Values(@i,'firstname_xxx','lastname_xxx','country_xxx','note_xxx') -     Set @i=@i+1 - End + SET Identity_insertTestTableOFF

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

1 SELECT TOP Ten *2  fromtesttable3 WHERE(ID not inch4(SELECT TOP  -ID5           fromtesttable6          ORDER  byID))7 ORDER  byID8 9 SELECT TOPPage size*Ten  fromtesttable One WHERE(ID not inch A(SELECT TOPPage size*page ID -           fromTable -          ORDER  byID)) the ORDER  byId

Paging Scenario Two: (using ID greater than how much and select top paging)

1 SELECT TOP Ten *2  fromtesttable3 WHERE(ID>4(SELECT MAX(ID)5           from(SELECT TOP  -ID6                   fromtesttable7                  ORDER  byId asT))8 ORDER  byID9 Ten SELECT TOPPage size* One  fromtesttable A WHERE(ID> -(SELECT MAX(ID) -           from(SELECT TOPPage size*page ID the                   fromTable -                  ORDER  byId asT)) - ORDER  byId

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

1 Create  procedureXiaozhengge2 @sqlstr nvarchar(4000),--query string3 @currentpage int,--Page N4 @pagesize int --number of rows per page5  as6 SetNocount on7 Declare @P1 int,--P1 is the ID of the cursor8  @rowcount int9 execSp_cursoropen@P1Output@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcountOutputTen Select Ceiling(1.0*@rowcount/@pagesize) asTotal pages--, @rowcount as total number of rows, @currentpage as current page One Set @currentpage=(@currentpage-1)*@pagesize+1 A 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 the efficiency will be low.
When tuning is recommended, the query efficiency increases with the primary key and index.

Summarize:
Paging Scenario Two: (using ID greater than how much and select top paging) The most efficient, need to splice SQL statements
Paging Scenario One: (using not and select top paging) second, the need to splice SQL statements
Paging Scenario Three: (Paging with SQL cursor stored procedures) is the least efficient, but the most common
In the actual situation, to be specific analysis.

Three pagination methods and summaries in SQL SERVER 2008