Return total records from SQL Server when using ROW_NUMBER

Question

I would like to return the total number of records in the database so I can set up pagination. How do I return the total number of records in the DB when using the following paging method in SQL Server 2008?

    ALTER PROCEDURE [dbo].[Nop_LoadAllOptimized]
    (
        @PageSize int = 20,
        @PageNumber int = 1,
        @WarehouseCombinationID int = 1,
        @CategoryId int = 58,
        @OrderBy int = 0,
        @TotalRecords int = null OUTPUT
    )
    AS
    BEGIN
    WITH Paging AS (
        SELECT rn = (ROW_NUMBER() OVER (
        ORDER BY 
            CASE WHEN @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
            THEN pcm.DisplayOrder END ASC,
            CASE WHEN @OrderBy = 0
            THEN p.[Name] END ASC,
            CASE WHEN @OrderBy = 5
            THEN p.[Name] END ASC,
            CASE WHEN @OrderBy = 10
            THEN wpv.Price END ASC,
            CASE WHEN @OrderBy = 15
            THEN wpv.Price END DESC,
            CASE WHEN @OrderBy = 20
            THEN wpv.Price END DESC,
            CASE WHEN @OrderBy = 25
            THEN wpv.UnitPrice END ASC  
        )), p.*, pcm.DisplayOrder, wpv.Price, wpv.UnitPrice FROM Nop_Product p
        INNER JOIN Nop_Product_Category_Mapping pcm ON p.ProductID=pcm.ProductID
        INNER JOIN Nop_ProductVariant pv ON p.ProductID = pv.ProductID
        INNER JOIN Nop_ProductVariant_Warehouse_Mapping wpv ON pv.ProductVariantID = wpv.ProductVariantID
        WHERE pcm.CategoryID = @CategoryId AND (wpv.Published = 1 AND pv.Published = 1 AND p.Published = 1 AND p.Deleted = 0)
        AND wpv.WarehouseID IN (select WarehouseID from Nop_WarehouseCombination where UserWarehouseCombinationID = @WarehouseCombinationID)    
    )
    SELECT TOP (@PageSize) * FROM Paging PG
    WHERE PG.rn > (@PageNumber * @PageSize) - @PageSize 

    SET @TotalRecords = @@ROWCOUNT 

    END

Cum

See Efficient way of getting @@rowcount from a query using row_number – Extent

@Martin thats what I was looking at but it doesn't say how to return the total number of rows? – Cum

I edited my comment and changed the link. Which link are you referring to? The SQLServerCentral one or the StackOverflow one? BTW It is part 2 of the SQL Server Central article that covers this. – Extent

@Martin, Thanks the edit makes sense. That works, it adds a TotalRows column to my record set. How can I assign the TotalRows as an output parameter? – Cum

Answer 1

I typically do it this way - never really checked whether it's very efficient from a performance point of view:

WITH YourCTE AS 
(
   SELECT 
       (list of columns),
       ROW_NUMBER() OVER (ORDER BY ......) AS 'RowNum' 
   FROM dbo.YourBaseTable
)
SELECT 
    *,
    (SELECT MAX(RowNum) FROM YourCTE) AS 'TotalRows' 
FROM 
    YourCTE
WHERE   
    RowNum BETWEEN 101 AND 150

Basically, the RowNum value will have values 1 through the total of rows (if you don't have a PARTITION BY in your CTE) and thus selecting MAX(RowNum), you get the total number of rows.

Cumulous

Thanks. How do you assign the TotalRows to an output parameter from a store proc? It works both ways, yours and Martins, but would like to return it as a param rather than a new column in my dataset. EDIT, got it: @TotalRecords = PG.TotalRows – Cum

Very clever! Works great (and fast) for me. – Maker

You should replace the Max(RowNum) field with Count(*). Count performs a lot faster, at least in my queries. – Weidar

How do i assign TotalRows to the @TotalRecords int = null OUTPUT ? – Infusorian

Invalid object name 'MyCTE'. I tried this SELECT @TotalRecords = (SELECT MAX(RowNumber) FROM MyCTE) SELECT Id, Name FROM MyCTE WHERE RowNumber BETWEEN Start AND End . I think CTE will be available for only one immediate SELECT statement – Infusorian

Answer 2

Well I had used #temp tables in past but found out another solution using Count(..) OVER() to find total number of rows few days ago. It works something like this:

SELECT COUNT(id) OVER() [Total], ROW_NUMBER() OVER(ORDER BY id DESC) [RowNo]
, [other columns]
FROM Table

The reference page is here.

Discourse

This is the best solution I have found for this problem thus far. Has the benefit of not significantly impacting query execution time. – Throckmorton

The problem here is that RowNo cannot be used inside WHERE clause. – Barr

I know this is old but this solution is much better than the one presented above. Fedor pointed out that RowNo cannot be used inside WHERE clause, but it can be used if this query is enclosed: SELECT * FROM (TheVillageIdiotsQuery) AS SubQuery WHERE RowNo BETWEEN 1 AND 2 – Delbert