Data paging for SQL Server:
Suppose you now have a table like this:
CREATE TABLE Test ( intprimarykeynotnullidentity, varchar ())
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 pagingSelect Top Ten * fromTestwhereId not inch( --40 is calculated as: 10* (5-1) Select Top +Id fromTestOrder byID)Order byId
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 aboveSelect Top Ten * fromTestwhereId>( Select IsNull(Max(ID),0) from ( Select Top +Id fromTestOrder byID) A)Order byId
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 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 procedureAlter 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 proceduresexec Pagedemo Ten,5
Four ways of implementing SQL Server paging query