SQL Paginación/Total Virtual/Paging

Link artículo

--Total Virtual

COUNT(*) OVER ( ) AS TotalVirtual

----------------------------------------------

USE AdventureWorks2012

GO

--------------------------------------------------

-- SQL Server 2012

--------------------------------------------------

DECLARE @RowsPerPage INT = 10 ,

    @PageNumber INT = 6

SELECT  SalesOrderDetailID ,

        SalesOrderID ,

        ProductID

FROM    Sales.SalesOrderDetail

ORDER BY SalesOrderDetailID

OFFSET (@PageNumber-1)*@RowsPerPage ROWS

FETCH NEXT @RowsPerPage ROWS ONLY

GO

--------------------------------------------------

-- SQL Server 2008 / R2

-- SQL Server 2005

--------------------------------------------------

DECLARE @RowsPerPage INT = 10 ,

    @PageNumber INT = 6

SELECT  SalesOrderDetailID ,

        SalesOrderID ,

        ProductID

FROM    ( SELECT    SalesOrderDetailID ,

                    SalesOrderID ,

                    ProductID ,

                    ROW_NUMBER() OVER ( ORDER BY SalesOrderDetailID ) AS RowNum

          FROM      Sales.SalesOrderDetail

        ) AS SOD

WHERE   SOD.RowNum BETWEEN ( ( @PageNumber - 1 ) * @RowsPerPage ) + 1

                   AND     @RowsPerPage * ( @PageNumber )

GO

--------------------------------------------------

-- SQL Server 2000

--------------------------------------------------

DECLARE @RowsPerPage INT = 10 ,

    @PageNumber INT = 6

SELECT  SalesOrderDetailID ,

        SalesOrderID ,

        ProductID

FROM    ( SELECT TOP ( @RowsPerPage )

                    SalesOrderDetailID ,

                    SalesOrderID ,

                    ProductID

          FROM      ( SELECT TOP ( ( @PageNumber ) * @RowsPerPage )

                                SalesOrderDetailID ,

                                SalesOrderID ,

                                ProductID

                      FROM      Sales.SalesOrderDetail

                      ORDER BY  SalesOrderDetailID

                    ) AS SOD

          ORDER BY  SalesOrderDetailID DESC

        ) AS SOD2

ORDER BY SalesOrderDetailID ASC

GO

-------------

USE AdventureWorks2012
GO--------------------------------------------------
-- SQL Server 2012
--------------------------------------------------DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6SELECT SalesOrderDetailID, SalesOrderID, ProductIDFROM Sales.SalesOrderDetailORDER BY SalesOrderDetailID
OFFSET (@PageNumber-1)*@RowsPerPage ROWSFETCH NEXT @RowsPerPage ROWS ONLYGO--------------------------------------------------
-- SQL Server 2008 / R2
-- SQL Server 2005
--------------------------------------------------DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6SELECT SalesOrderDetailID, SalesOrderID, ProductIDFROM (SELECT SalesOrderDetailID, SalesOrderID, ProductID,ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNumFROM Sales.SalesOrderDetail ) AS SODWHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1AND @RowsPerPage*(@PageNumber)GO--------------------------------------------------
-- SQL Server 2000
--------------------------------------------------DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6SELECT SalesOrderDetailID, SalesOrderID, ProductIDFROM(SELECT TOP (@RowsPerPage)SalesOrderDetailID, SalesOrderID, ProductIDFROM(SELECT TOP ((@PageNumber)*@RowsPerPage)SalesOrderDetailID, SalesOrderID, ProductIDFROM Sales.SalesOrderDetailORDER BY SalesOrderDetailID) AS SODORDER BY SalesOrderDetailID DESC) AS SOD2ORDER BY SalesOrderDetailID ASCGO