SQL Server uses the row_number paging implementation method, sqlrow_number

SQL Server uses the row_number paging implementation method, sqlrow_number

This article will share with you how SQL Server uses row_number Paging for your reference. The specific content is as follows:

1. First

Select ROW_NUMBER () over (order by id asc) as 'rownumber', * from table1

Generate a set with serial numbers

2. query 1st to 5th data records of the set.

  select * from   (select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1) as temp  where rowNumber between 1 and 5

Complete SQL statement

Declare @ pagesize int; declare @ pageindex int; set @ pagesize = 3 set @ pageindex = 1; -- select * from (select ROW_NUMBER () over (order by id asc) on the first page) as 'rownumber', * from table1) as temp where rowNumber between (@ pageindex-1) * @ pagesize) + 1) and (@ pageindex * @ pagesize) set @ pageindex = 2; -- select * from (select ROW_NUMBER () over (order by id asc) as 'rownumber', * from table1) as temp where rowNumber between (@ pageindex-1) * @ pagesize) + 1) and (@ pageindex * @ pagesize) set @ pageindex = 3; -- page 3 select * from (select ROW_NUMBER () over (order by id asc) as 'rownumber', * from table1) as temp where rowNumber between (@ pageindex-1) * @ pagesize) + 1) and (@ pageindex * @ pagesize) set @ pageindex = 4; -- select * from (select ROW_NUMBER () over (order by id asc) on the fourth page) as 'rownumber', * from table1) as temp where rowNumber between (@ pageindex-1) * @ pagesize) + 1) and (@ pageindex * @ pagesize)

Next we will write a stored procedure page.

Alter Procedure PagePager @ TableName varchar (80), @ File varchar (1000), --- @ Where varchar (500), --- connect with and @ OrderFile varchar (100 ), -- sorting field @ OrderType varchar (10), -- asc: Order, desc: reverse @ PageSize varchar (10), -- @ PageIndex varchar (10) -- as if (ISNULL (@ OrderFile, '') ='') begin set @ OrderFile = 'id'; end if (ISNULL (@ OrderType, '') = '') begin set @ OrderType = 'asc 'end if (ISNULL (@ File, '') ='') begin set @ File = '*' end declare @ select varchar (8000) set @ select = 'select' + @ File + 'from (select *, ROW_NUMBER () over (order by' + @ OrderFile + ''+ @ OrderType + ') as ''rownumber'' from '+ @ TableName + 'where 1 = 1' + @ where +') temp Where rowNumber between ('+ @ PageIndex +'-1) * '+ @ PageSize +') + 1) and ('+ @ PageIndex +' * '+ @ PageSize +') 'exec (@ select)

The above is all the content in this article. I hope it will help you learn row_number paging.