Four ways to paging SQL Server

The first type: Row_number () over () mode

SELECT * FROM (
SELECT *, Row_number () over (Order by ArtistID) as RowId from Artistmodels
) as B

where RowId between and 20
　　---where RowId between the current number of pages -1* and pages * Number of bars---

The execution results are:

The second way: offset fetch next mode (SQL2012 more than the version is supported: recommended)

SELECT * from Artistmodels ORDER by ArtistID offset 4 rows fetch next 5 rows only
--order by ArtistID offset page rows fetch Next number rows only----

The execution results are:

Third Way:--top not in mode (adapts to versions below database 2012)

Select Top 3 * from Artistmodels
where ArtistID not in (select Top ArtistID from Artistmodels)

------where Id not in (select Top Bar * Number of pages ArtistID from artistmodels)

Execution Result:

Fourth Way: Paging with stored procedures

CREATE procedure Page_demo
@tablename varchar (20),
@pageSize int,
@page int
As
DECLARE @newspage int,
@res varchar (100)
Begin
Set @[email protected]* (@page-1)
Set @res = ' select * from ' [e-mail protected]+ ' ORDER by ArtistID offset ' +cast (@newspage as varchar) + ' rows fetch NEX T ' + CAST (@pageSize as varchar) + ' rows only '
EXEC (@res)
End
EXEC Page_demo @tablename = ' artistmodels ', @pageSize =3, @page =5

Execution Result:

PS: Today engaged in the afternoon of the page, through the Internet to check the data and their own experiments, summed up four ways for everyone to refer to the page, there are problems for everyone to exchange learning

Four ways to paging SQL Server