CODEWITHSUNDEEP
sql-serverpaginationsql-server-2012
LCJ
LCJ

Reputation: 22652

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

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

Upvotes: 37

Views: 51358

Answers (2)

Majid Zandi
Majid Zandi

Reputation: 4581

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/

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239674

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.

Upvotes: 69

Related Questions