SQL Server paging Solution

Paging solution 1: (use not in and select top pages)

Example:

10 records are displayed on each page, and the first page is displayed:

 

Select   Top   10   *   From Production. Product Where
(Productid Not   In ( Select   Top   0 Productid From Production. Product Order   By Productid ))
Order   By Productid

The result is as follows:

 

 

 

10 records are displayed on each page, and the second page is displayed:

Select   Top   10   *   From Production. Product Where  
(Productid Not   In ( Select   Top   10 Productid From Production. Product Order   By Productid ))
Order   By Productid

The result is as follows:

 

It can be summarized:

Select   Top Page size *   From   Table   Where  
(ID Not   In ( Select   Top Page size * (Page number - 1 ) ID From   Table   Order   By ID ))
Order   By ID

 

Paging solution 2: (use the row_number () page added by SQL Server 2005)

Example:

10 records are displayed on each page, and the first page is displayed:

With Products As
( Select  
Row_number () Over ( Order   By Productid) As Rownumber, *
From Production. Product)
Select   *
From Products
Where Rownumber Between   1   And   10 ;

The result is as follows:

 

 

10 records are displayed on each page, and the second page is displayed:

 

With Products As
( Select  
Row_number () Over ( Order   By Productid) As Rownumber, *
From Production. Product)
Select   *
From Products
Where Rownumber Between   11   And   20 ;

The result is as follows:

 

It can be summarized:

 

With Products As
( Select  
Row_number () Over ( Order   By Productid) As Rownumber, *
From Production. Product)
Select   *
From Products
Where Rownumber Between Page size * (Page number - 1 ) + 1   And Page size * Number of pages;