Getting total row count from OFFSET / FETCH NEXT

So, I've got a function that returns a number of records that I want to implement paging for on my website. It was suggested to me that I use the Offset/Fetch Next in SQL Server 2012 to accomplish this. On our website, we have an area that lists total number of records and what page you're on at the time.

Before, I was getting the entire record set and was able to build the paging on that programatically. But using the SQL way with FETCH NEXT X ROWS ONLY, I am only given back X rows, so I don't know what my total record set is and how to calculate my min and max pages. The only way I can tell of doing this is calling the function twice and doing a count of rows on the first, then running the second with FETCH NEXT. Is there a better way that won't have me running the query twice? I am trying to speed up performance, not slow it down.

Solution

You can use COUNT(*) OVER() ... here is a quick example using sys.all_objects:

DECLARE 
  @PageSize INT = 10, 
  @PageNum  INT = 1;

SELECT 
  name, object_id, 
  overall_count = COUNT(*) OVER()
FROM sys.all_objects
ORDER BY name
  OFFSET (@PageNum-1)*@PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

However, this should be reserved for small data sets; on larger sets, the performance can be abysmal. See this Paul White article for better alternatives, including maintaining indexed views (which only works if the result is unfiltered or you know WHERE clauses in advance) and using ROW_NUMBER() tricks.