Sql best way to pagination

Question

I have #temp table and I need paginate all data in this table. How best way to table pagination?

create table #temp (
    Id int
    ,SomeName nvarchar(100)
)

create table #tempPage (
    Id int
    ,SomeName nvarchar(100)
    ,PageIndex int
)
--Test data
insert #temp (Id, SomeName) values
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'F'),
(6,'G'),
(7,'H'),
(8,'A1'),
(9,'B1'),
(10,'C1'),
(11,'D1'),
(12,'F1'),
(13,'G1'),
(14,'H1');

--Page size
declare @PageSize int = 5

--Max page count
declare @MaxPages float = (
    select
        case when count(Id)%@PageSize>0 then count(Id)/@PageSize+1 else count(Id)/@PageSize end
    from #temp
)

declare @PageFrom int = 0

declare @CurrentPage int = 1

while @CurrentPage <= @MaxPages
begin
    insert #tempPage (Id, SomeName, PageIndex)
    SELECT
        Id, SomeName, @CurrentPage
    FROM #temp
    ORDER BY id OFFSET @PageFrom ROWS
    FETCH NEXT @PageSize ROWS ONLY;
    set @PageFrom = @PageFrom + @PageSize
    set @CurrentPage = @CurrentPage + 1

end

select * from #tempPage

drop table #temp
drop table #tempPage

Result:

It's work very slow on big data. I use Ms Sql 2012.

You have a page size of `5`. A well design database can handle millions is rows in a second; you want a much larger page than 5. — Thom A, Nov 10 '20 at 17:01
truncate the row number divided by the number of records you want per page to determine your page index. avoiding a while loop. your performance problem is because you're operating on a record by record basis when set operations work so much faster. As a rule of thumb, if it can be done as a set operation do it that way. — xQbert, Nov 10 '20 at 17:11
Does this answer your question? [What is the best way to paginate results in SQL Server](https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) — Michael Freidgeim, May 18 '22 at 23:54

score 5 · Answer 1 · answered Nov 10 '20 at 17:39

You can use the OFFSET & FETCH NEXT feature, The OFFSET keyword brings only from row_number and FETCH NEXT brings until. For an instance:

 USE AdventureWorks2008R2
    GO
    SELECT 
      BusinessEntityID
      ,PersonType
     ,FirstName + ' ' + MiddleName + ' ' + LastName 
    FROM Person.Person
     ORDER BY BusinessEntityID ASC
      OFFSET 100 ROWS 
      FETCH NEXT 5 ROWS ONLY
    GO

In that way you can make use of parameters for pagination, for instance:

SELECT
  BusinessEntityID
 ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID
  OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
  FETCH NEXT @RowCountPerPage ROWS ONLY
GO

For deeper understanding and performance you can read this - Pagination with OFFSET / FETCH : A better way article

Had fogotten about this myself thanks for the remiinder! I'd go as far as saying this should be the answer. — xQbert, Nov 11 '20 at 13:58

xQbert · Accepted Answer · 2020-11-10T17:35:32.320

Set based operations perform better. Avoid row by row processing.

We can use row_number assigned by the database and divide by the number of records we want per page to generate a page index. If we truncate/(round and eliminate decimals) we get the desired page index.

Something Like:

SELECT ID
    , SomeName
    , round(ROW_NUMBER() OVER(ORDER BY SomeName ASC)/5,0,1) AS PageIndex
FROM #temp
ORDER BY PageIndex, SomeName

5 represents number of records per "page"
0 as we don't care about the decimals but we don't want rounding to occur before truncating the decimals.
1 to truncate to 0 decimals w/o rounding.

I assume you know you could wrap this in a CTE and add a where clause to get specific pages desired

score 0 · Answer 3 · answered Nov 10 '20 at 17:32

As you are looking for the best approach for pagination and you correctly said your solution is slow for large number of data you have to get one page of data at a time based on the user request.

When the user click page 2 you will get records for page 2 only not all.

Here is the sample code for this

declare @PageNumber int = 2; -- just an example, will come from parameter
declare @PageTo int = @PageSize * @PageNumber;
declare @PageFrom int = @PageTo - @PageSize;

    SELECT
        Id, SomeName, @PageNumber
    FROM #temp
    WHERE Id > @PageFrom AND Id <= @PageTo

drop table #temp

Sql best way to pagination

3 Answers3