over - Devuelve los registros totales de SQL Server al usar ROW_NUMBER

sql get row_number

Question

Me gustaría devolver el número total de registros en la base de datos para poder configurar la paginación. ¿Cómo devuelvo el número total de registros en el DB cuando uso el siguiente método de paginación en 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

Answer 1

Bueno, yo había usado tablas #temp en el pasado pero descubrí otra solución usando Count(..) OVER() para encontrar el número total de filas hace unos días. Funciona de la siguiente manera:

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

La página de referencia está aquí .

Answer 2

Normalmente lo hago de esta manera, nunca realmente comprobé si es muy eficiente desde el punto de vista del rendimiento:

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

Básicamente, el valor RowNum tendrá valores de 1 a través del total de filas (si no tiene un PARTITION BY en su CTE) y seleccionando MAX(RowNum) , obtendrá el número total de filas.