Four ways to query SQL pages

Method One

Suppose you now have a table like this:

CREATE TABLE  intprimarykeynotnullidentity    varchar())

And then insert about 100 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 Ten *  from Test where  not inch  --40 is calculated as: 10* (5-1)selecttop  order by ID)Theorder 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.

Method Two

Take the above results as an example, using a different method
-The meaning of the data is the same as mentioned above

Select Top Ten * fromTestwhereId>( Select IsNull(Max(ID),0)  from   (   Select Top  +Id fromTestOrder  byID) A)Order  byId

Principle: First 40 records are queried, then the maximum ID value is obtained, if the ID value is NULL, then return 0
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.

Method Three

Select Top Ten *  from  Selectover (order by as RowNumber,*from  test) Awhere>

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 procedurePagedemo@pageSize int,@page int asDeclare @temp intSet @temp=@pageSize*(@page - 1)begin Select Top(Select @pageSize)*  fromTestwhereId not inch(Select Top(Select @temp) ID fromTestOrder  byIDEnd

Executing stored procedures
EXEC 10,5

Four ways to query SQL pages