SQL Server Paging statements

The primary key in the table must be an identity column, [ID] int identity (+)

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

 select *< Span style= "color: #0000ff;" >from Testtablewhere (ID not in select top (page size * (page -< Span style= "color: #800000; Font-weight:bold; " >1)) ID from table order by ID ") order Span style= "color: #0000ff;" >by ID                

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

SELECTTOP Page Size*from Testtablewhere ( ID >select max (ID) from (select Span style= "color: #0000ff;" >top (page size * (pages -1)) ID from table order as T)) Span style= "color: #0000ff;" >order by ID           

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

CreateProcedureSqlPager@sqlstrnvarchar4000),--Query string@currentpageInt--Page n@pagesizeInt--Number of rows per pageAsSET NOCOUNTOnDeclare@P1Int--P1 is the ID of the cursor@rowcountIntEXEC sp_cursoropen@P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcountOutputSelectCeiling1.0*@rowcount/@pagesize)As Total pages--set  @currentpage = ( @currentpage - @pagesize + 1exec sp_cursorfetch  @P1, 16, @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 the efficiency will be low. When tuning is recommended, the query efficiency increases with the primary key and index.

Using SQL Query Analyzer to show comparisons: My conclusion is:

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) The least efficient

Category: SQL Server

SQL Server Paging statements