SQL – Best way to get Total Count with pagination

Paginated query to SQL server is a very common logic that might be used literally everywhere.

After googling a bit, the code below seems to be the best practice in 2020. The key point here is using Count(*) Over which allows getting list and total count at a single query.

Select *, Count(*) Over () AS TotalCount
From database
Where your condition
Order By name
Offset (@pageNumber - 1) * @pageSize Rows
Fetch Next @pageSize Rows Only

If the query have to return total count as an out parameter, it might be best to save the paged result in #tempTable and read the first value from the TotalCount column and set it to the out parameter.

Ref: https://stackoverflow.com/questions/18119463/better-way-for-getting-total-count-along-with-paging-in-sql-server-2012


    1. That’s a good point. I haven’t tested the code against the large tables like 22M rows. I’m curious how you solved your performance.

Leave a comment

Your email address will not be published. Required fields are marked *