Getting the total number of rows from OFFSET / FETCH NEXT

Question

So, I have a function that returns several records that I want to implement swap on my site. I was suggested that I use Offset / Fetch Next in SQL Server 2012 to accomplish this. On our website, we have an area that shows the total number of entries and which page you are at that time.

I used to get the whole set of records and was able to program paging. But using the SQL method with FETCH NEXT X ROWS ONLY, they give me only X rows, so I don’t know what my total recordset is and how to calculate my minimum and maximum pages. The only way I can say this is to call the function twice and count the lines on the first and then run the second with FETCH NEXT. Is there a better way that won't force me to execute the request twice? I'm trying to speed things up, not slow it down.

Answer 1

You can use COUNT(*) OVER() ... here is a short example of 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 large kits, performance can be terrible. See this article by Paul White for better alternatives , including support for indexed views (which only works if the result is not filtered or you know WHERE clauses beforehand) and using the ROW_NUMBER() tricks.

Answer 2

I ran into some performance issues using the COUNT () OVER () method. (I'm not sure if it was a server, since it took 40 seconds to return 10 records, and then there were no problems.) This method worked under any conditions without using COUNT () OVER () and does the same:

 DECLARE @PageSize INT = 10, @PageNum INT = 1; WITH TempResult AS( SELECT ID, Name FROM Table ), TempCount AS ( SELECT COUNT(*) AS MaxRows FROM TempResult ) SELECT * FROM TempResult, TempCount ORDER BY TempResult.Name OFFSET (@PageNum-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY 

Answer 3

Based on James Moberg's answer :

This is an alternative using Row_Number() if you do not have 2012 SQL Server and you cannot use OFFSET

 DECLARE @PageNumEnd INT = 10, @PageNum INT = 1; WITH TempResult AS( SELECT ID, NAME FROM Tabla ), TempCount AS ( SELECT COUNT(*) AS MaxRows FROM TempResult ) select * from ( SELECT ROW_NUMBER() OVER ( ORDER BY PolizaId DESC) AS 'NumeroRenglon', MaxRows, ID, Name FROM TempResult, TempCount )resultados WHERE NumeroRenglon >= @PageNum AND NumeroRenglon <= @PageNumEnd ORDER BY NumeroRenglon