Total row count with OFFSET .. FETCH issue

Question

Goal is trivial: get total row count and some data page.

When I use OFFSET...FETCH approach to implement paging with total row counting I running into following issue: when we pass some big page number (e.g. we have only 100 rows, but requested 15th with 10 records per page) COUNT(*) OVER() statement has never called, because result set is empty. So, we can not get right total row count in this case.

Is there way to get right total row count using OFFSET ... FETCH approach even when big page number passed?

FYI, OFFSET ... FETCH approach is that:

SELECT 
  ...
  Total = COUNT(*) OVER()
FROM Table1
ORDER BY Col1
  OFFSET (@PageNum-1) * @PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

Answer 1

I think the answer is "no". You are appending the total row count onto each row being returned. The query is returning no rows, so there is no place to put the total.

By the way, I do imagine that the total is being calculated. But without any rows, you never see it.

EDIT:

The only work-around I can think of is at the application layer. If no rows are returned, then run:

SELECT Total = COUNT(*) OVER()
FROM Table1;

You could actually run this first to get the total. The downside is when the table really isn't a table but a view that is expensive to run.