Paging statements in SQL Server databases

Two paging SQL statements

The first one is the. Not in

 

Select Top 2 * From xy_role where id not in (select Top 4 ID from xy_role)

 

'2' indicates the number of information entries displayed on each page. '4': for example, if the current page is 3 pages, it is (3-1) * (each number)

Remember to sort and filter the data in the subquery if you want to sort the data.

 

Type 2: Use the row_number () function in sqlserver2005 or later versions ()

Select Top 2 * from (select *, row_number () over (order by id desc) as Ro from xy_role) as awhere A. Ro> 4 order by ID DESC

Row_number () is used to arrange data in a sequential number, such as 1, 2, 3, 4, 5... in this case, Table A has a column named Ro,

Use Where a. Ro> 4 to get the expected result.

 

The performance should be better.