Better way for Getting Total Count along with Paging in SQL Server 2012

Question

I have requirement to get the total count of records along with paging. At present I am doing it as listed below in SQL Server 2012. This needs a separate query for getting count. Is there any improved way in SQL Server 2012?

ALTER PROCEDURE dbo.tpGetPageRecords
(
    @OffSetRowNo INT,     
    @FetchRowNo INT,
    @TotalCount INT OUT
) 
AS 

SELECT CSTNO, CSTABBR 
FROM DBATABC
WHERE CSTABBR LIKE 'A%'
ORDER BY CSTNO
OFFSET ( @OffSetRowNo-1 ) * @FetchRowNo ROWS
FETCH NEXT @FetchRowNo ROWS ONLY

SET @TotalCount = 
(SELECT COUNT(*)
FROM DBATABC
WHERE CSTABBR LIKE 'A%')


GO

Are we allowed to change *how* the total count is returned back (I.e. no longer pass it back as an output parameter)? — Damien_The_Unbeliever, Aug 08 '13 at 06:42
Does this answer your question? [Getting total row count from OFFSET / FETCH NEXT](https://stackoverflow.com/questions/12352471/getting-total-row-count-from-offset-fetch-next) — lcnicolau, Aug 05 '21 at 15:21

Damien_The_Unbeliever · Accepted Answer · 2013-08-08T06:59:53.667

62

If we're allowed to change the contract, you can have:

SELECT CSTNO, CSTABBR,COUNT(*) OVER () as TotalCount
FROM DBATABC
WHERE CSTABBR LIKE 'A%'
ORDER BY CSTNO
OFFSET ( @OffSetRowNo-1 ) * @FetchRowNo ROWS
FETCH NEXT @FetchRowNo ROWS ONLY

And now the total will be available as a separate column in the result set. Unfortunately, there's no way to assign this value to a variable in this same statement, so we can no longer provide it as an OUT parameter.

This uses the OVER clause (available since 2005) to allow an aggregate to be computed over the entire (unlimited) result set and without requiring GROUPing.

edited Aug 08 '13 at 06:59

answered Aug 08 '13 at 06:54

Damien_The_Unbeliever

234,701
27
340
448

Thanks.. It works.... How about the performance in general. Is this approach going to produce a fatser result? – LCJ Aug 08 '13 at 06:59
1

@Lijo - performance is a difficult thing to make general statements about - without having your tables, your data, knowing what exact versions (including service packs, hotfixes, etc) you're running on, what hardware, etc. All I can say is that I'd be remarkably surprised if it performed worse than your current one. – Damien_The_Unbeliever Aug 08 '13 at 07:04
1

@Lijo, there is a lot of ifs and buts here but I have yet to see a case where the inline count outperform the two queries in the question. All rows matched in the where clause goes to a lazy spool that is then used twice. Once for the count and once to get all the rows again for the top operator. Bottom line is you need to test on the data you have. – Mikael Eriksson Aug 08 '13 at 07:59
17

Performance is AWFUL!! Tested in a table with 1 mil records 2268ms vs 9ms normal count + 68ms (the actual select taking just a 100 records page) – DATEx2 Sep 11 '14 at 08:50
How would this look like if the user could opt to have the inline count or not though a Boolean variable? This is a common thing in OData where count is optional, so I might need to provide the count in the procedure based on an input parameter. – julealgon Oct 09 '18 at 03:13
I have a similar requirement but I am stuck with SQL 2008. any suggestions on how to do it with 2008? – superjugy Oct 25 '18 at 13:00
5

Have a look at this article: [SQL Server 2005 Paging – The Holy Grail](https://www.sqlservercentral.com/articles/sql-server-2005-paging-%e2%80%93-the-holy-grail), where s/he explains the reason for the poor performance, and also gives alternatives: "In this case, SQL Server implements the COUNT(*) OVER() by dumping all the data into a hidden spool table, which it then aggregates and joins back to your main output." – Christian Davén Feb 11 '20 at 07:17

score 2 · Answer 2 · answered Mar 06 '22 at 15:32

WITH Data_CTE 
AS
(
    SELECT [name], object_id
    FROM sys.all_objects
    --WHERE [name] LIKE 'fn_%'
), 
Count_CTE 
AS 
(
    SELECT COUNT(*) AS TotalRows FROM Data_CTE
)
SELECT *
FROM Data_CTE
CROSS JOIN Count_CTE
ORDER BY [name]
OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

http://andreyzavadskiy.com/2016/12/03/pagination-and-total-number-of-rows-from-one-select/ https://raresql.com/2015/03/30/sql-server-how-to-get-total-row-count-from-offset-fetch-next-paging/

Better way for Getting Total Count along with Paging in SQL Server 2012

2 Answers2

Linked