Summary of four paging methods based on sqlserver
The first way: ROW_NUMBER () OVER ()
Select * from (
Select *, ROW_NUMBER () OVER (Order by ArtistId) AS RowId from ArtistModels
) as b
Where RowId between 10 and 20
-current number of where RowId BETWEEN pages-1 * number of and pages * number of pages--
The result of the execution is:
The second method: offset fetch next (only supported by versions above SQL2012: recommended)
Select * from ArtistModels order by ArtistId offset 4 rows fetch next 5 rows only
-- number of order by ArtistId offset pages, number of rows fetch next entries rows only
The result of the execution is:
The third way:-- top not in mode (suitable for database versions below 2012)
Select top 3 * from ArtistModels
Where ArtistId not in (select top 15 ArtistId from ArtistModels)
-where Id not in (number of select top * pages ArtistId from ArtistModels)
Execution result:
The fourth way: paging with stored procedures
CREATE procedure page_Demo
@ tablename varchar (20)
@ pageSize int
@ page int
AS
Declare @ newspage int
@ res varchar
Begin
Set @ newspage=@pageSize* (@ page-1)
Set @ res='select * from'+ @ tablename+ 'order by ArtistId offset' + CAST (@ newspage as varchar (10)) + 'rows fetch next' + CAST (@ pageSize as varchar (10)) + 'rows only'
Exec (@ res)
End
EXEC page_Demo @ tablename='ArtistModels',@pageSize=3,@page=5
Execution result:
Ps: I have been paging all afternoon. Through searching materials on the Internet and my own experiments, I have summed up four paging methods for your reference. If you have any questions, we will communicate and study together.
