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.

Answers


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;

Need Your Help

Scrolling Richtextblock in ContentDialog

c# xaml windows-8.1 winrt-xaml windows-10

In my UWP application I've got a RichTextBlock inside ContentDialog and I set some sample Height for each element to check if it works or not.

Writing from System.out.println to a file

java file createfile

I've replaced many strings and outputted the result and now am trying to write those lines into a text file. Here's what I did. I created a new file: