This is well covered in many other places, but I’m posting on my own blog so that I can more easily find this trick.
You can do paging in SQL Server 2012 and later using the OFFSET-FETCH syntax. This is useful for returning a single page’s worth of records from a larger (sorted) set of records. To count the total number of records, however, you’d normally do a second query. The T-SQL below shows how to do a total count as part of the query that returns the page in question. The count is repeated for every record, but it avoids having to do two queries.
select FirstName, LastName, COUNT(*) over () as TotalCount from Person where LastName like 'mc%' order by LastName, FirstName offset 500 rows fetch next 100 rows only
Here’s what the output looks like, returning records 501-600 out of 1,968 records having a last name that starts with “Mc”.