Different Pagination Methods in SQL Server

-- ----------------------------------------------------------------------------
-- 1. First way is to use traditional CTE which uses ROW_NUMBER() with OVER
--    clause having required ORDER BY clauses. And then run a select query on
--    top of this CTE to get the results.
-- ----------------------------------------------------------------------------

create procedure dbo.GetRecentSalesOrdersMethod1(
    @PageNumber int,
    @PageSize int
)
as
begin
    set nocount on;

/* ----------------------------------------------------------------------------

Method 1 using CTE

Using CTE, we can get the TOP rows of our interest. And then we can query on
that CTE to get the range of records of our interest. ROW_NUMBER() with OVER
ORDER BY can is used to version each row, we have to use this to get the range
because we cannot use SalesOrderDetailId which can be or may be started from
different numbers. So we use RowNumber to get the range in the select statement.

---------------------------------------------------------------------------- */

    with PAGES as (
            select top (@PageNumber * @PageSize)
            SalesOrderDetailID,
            CarrierTrackingNumber,
            ROW_NUMBER() over(order by ModifiedDate desc,
            SalesOrderDetailID desc) as RowNumber from Sales.SalesOrderDetail
        )
        select SalesOrderDetailID,
            CarrierTrackingNumber
        from PAGES
        where RowNumber between(@PageNumber - 1) * @PageSize + 1
            and @PageNumber * @PageSize;
end;

-- ----------------------------------------------------------------------------
-- 2. Second approach is new in SQL Server 2012 which uses OFFSET and FETCH NEXT
--    ROWS ONLY to get results. Second approach is far more readable and easy to
--    understand, but for legacy SQL Server versions it is not available as it
--    is included only in 2012 version.
-- ----------------------------------------------------------------------------

create procedure dbo.GetRecentSalesOrdersMethod2(
    @PageNumber int,
    @PageSize int
)
as
begin
    set nocount on;

/* ----------------------------------------------------------------------------

Method 2 using OFFSET

IN SQL Server 2012, you can use OFFSET..FETCH to get the pagination. OFFSET
specifies the last record which needs to be ignored and from there the fetching
starts. We can specify FETCH NEXT with a number to get those many number of
records from offset.

---------------------------------------------------------------------------- */

    select SalesOrderDetailID,
           CarrierTrackingNumber
    from Sales.SalesOrderDetail
    order by ModifiedDate desc,
        SalesOrderDetailID desc
    offset((@PageNumber - 1) * @PageSize)
        rows fetch next @PageSize rows only;
end;

--
-- USAGE
--
exec dbo.GetRecentSalesOrdersMethod1 99, 10;
exec dbo.GetRecentSalesOrdersMethod1 99, 10;