SQL paging tips

1. Useful Paging for SQL2000 and sql2005

Method 1:

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

Method 2:

Select   Top Page size *
From Table1
Where ID >
(
Select   Isnull ( Max (ID ), 0 )
From  
(
Select   Top Page size * (PageQuantity - 1 ) ID From Table1 Order   By ID
) A
)
Order   By ID

 

2. Utility and sql2005

Note: This method is not suitable for paging after a large amount of data

Select   Top Page size *  
From  
(
Select Row_number () Over ( Order   By ID) As Rownumber, *   From Table1
)
Where Rownumber > Page size * (Page number - 1 )

 

Solution (refer to others' blogs)

Select   *   From   Table1 Where ID In (
Select ID From  
( Select ID, row_number () Over ( Order   By ID)
SCN From Table1 ) T
Where SCN < 100000   And SCN > 100000 - 20 )

In this way, the data between 99980 and 100000 can be retrieved, and the index with the ID can be quickly read.