Summary of four paging methods based on sqlserver and four paging methods of sqlserver

Summary of four paging methods based on sqlserver and four paging methods of sqlserver

First: ROW_NUMBER () OVER () method

Select * from (
Select *, ROW_NUMBER () OVER (Order by ArtistId) AS RowId from ArtistModels
) As B

Where RowId between 10 and 20

--- Where RowId BETWEEN current page number-1 * number of lines and number of pages * number of lines ---

The execution result is:

Method 2: offset fetch next (supported by SQL2012 and later versions: recommended)

Select * from ArtistModels order by ArtistId offset 4 rows fetch next 5 rows only
-- Order by ArtistId offset page number rows fetch next number rows only ----

The execution result is:

Method 3: -- top not in (applicable to versions earlier than database 2012)

Select top 3 * from ArtistModels
Where ArtistId not in (select top 15 ArtistId from ArtistModels)

------ Where Id not in (select top number of pages * ArtistId from ArtistModels)

Execution result:

Method 4: Paging Using Stored Procedures 

CREATE procedure page_Demo
@ Tablename varchar (20 ),
@ PageSize int,
@ Page int
AS
Declare @ newspage int,
@ Res varchar (100)
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:This afternoon, I conducted a paging operation. I checked the information online and my experiment. I summarized four paging methods for your reference. If you have any questions, I will share them with you.