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