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

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.

Leave a comment