Two Methods for querying by page on SQL Server

Two Methods of SQL Server paging query Abstract: SQL Server paging query is frequently used. The following describes how to implement two methods of SQL Server paging query, if you are interested in this, take a look.

Two Methods of querying by page in SQL Server:

SQL Server paging query is frequently used. The following describes the implementation methods of two SQL Server paging queries. If you are interested in this, take a look.

Pagesize: number of records displayed per page

Cureentpage: Current page number

 
 

  
  
select * from (   select TOP pagesize * FROM ( SELECT TOP pagesize*cureentpage   * from user_table   ORDER BY id ASC ) as aSysTable   ORDER BY id DESC ) as bSysTable   ORDER BY id ASC 
 
 

For example, you must select the 3,000th-page records in tbllendlist, with 100 records on each page.

----------

Method 1:

 
 

  
  
----------  
  
  
select top 100 * from tbllendlist   
  
  
where fldserialNo not in  
  
  
(  
  
  
select top 300100 fldserialNo from tbllendlist   
  
  
order by fldserialNo  
  
  
)  
  
  
order by fldserialNo 
 
 

Method 2:

 
 

  
  
----------  
  
  
SELECT TOP 100 *   
  
  
FROM tbllendlist   
  
  
WHERE (fldserialNo >   
  
  
(SELECT MAX(fldserialNo)   
  
  
FROM (SELECT TOP 300100 fldserialNo   
  
  
FROM tbllendlist   
  
  
ORDER BY fldserialNo) AS T))   
  
  
ORDER BY fldserialNo 
 
 

Two Methods of querying by page in SQL Server:

Method 1: Fast execution speed!