Data paging for SQL Server

Suppose you now have a table like this:

CREATE TABLE Test
(
ID int PRIMARY key NOT NULL identity,
Names varchar (20)
)
And then insert about 1000 data into it for the page test.
Assuming the number of pages is 10, now to take out the contents of page 5th, the query statement is as follows:
--10 represents the size of the paging
Select Top 10 *
From Test
where ID not in
(
--40 is calculated: 10* (5-1)
Select Top ID from Test order by ID
)
ORDER BY ID
Principle: Need to take out the 5th page of the database, is 40-50 records. First, take out the ID value of the first 40 records in the database, and then take out the first 10 elements of the remaining part.

The second method:
Take the above results as an example, using a different method
-The meaning of the data is the same as mentioned above
Select Top 10 *
From Test
where ID >
(
Select IsNull (max (ID), 0)
From
(
Select Top ID from Test order by ID
) A
)
ORDER BY ID
Principle: First 40 records are queried, and then the maximum ID value is obtained, and if the ID value is NULL, then 0 is returned.
Then the query ID value is greater than the record of the maximum ID value for the first 40 records.
This query has one condition, that is, the ID must be of type int.

The third method:
Select Top 10 *
From
(
Select Row_number () over (order by ID) as rownumber,* from test
) A
where RowNumber > 40
Principle: First sort all the data in the table according to a rownumber, then query rownuber more than 40 of the first 10 records
This approach is similar to a paging method in Oracle, but only supports versions of 2005 or more

The fourth type:
Stored Procedure queries
Create a stored procedure
Alter PROCEDURE Pagedemo
@pageSize int,
@page int
As
DECLARE @temp int
Set @[email protected]* (@page-1)
Begin
Select Top (select @pageSize) * FROM test where ID not in (select Top (select @temp) ID from test) Order by ID
End
Executing stored procedures
EXEC 10,5

Data paging for SQL Server