Pagination using ORDER BY

SQL Server 2012 now has additional features built into the ORDER BY Clause. This features makes pagination simpler and more efficient. Here is an example:

SELECT *
    FROM HumanResources.Employee E
ORDER BY E.EmployeeID ASC
OFFSET 55 ROWS
FETCH NEXT 30 ROWS ONLY;

Offset determines the start row to begin on, while the fetch next defines how many rows should be returned. Both parameters above can be variable driven. An example of this would be the following:

CREATE PROCEDURE dbo.spGetEmployees
(
    @StartRow int = 1,
    @RowCount int = 10
)
AS
BEGIN
    SET NOCOUNT ON;
   
    SELECT *
    FROM HumanResources.Employee E
    ORDER BY E.EmployeeID ASC
           OFFSET @StartRow ROWS
           FETCH NEXT @RowCount ROWS ONLY;
END

If it is in the case of previous version of Microsoft SQL Server, we are applying a normal query logic with using Row_Number and Common Table Expression keywords.

Declare @PageNumber            int = 1,
            @NumberOfRow         int = 20;

With cte
(
    RID,locCode,locName
)
As
(
    Select Row_Number() Over (Order By locPK) As RID,locCode,locName
    From dbo.AdmLocationMst
)

Select * From cte
Where RID Between (@PageNumber * @NumberOfRow) - (@NumberOfRow) + 1 And (@PageNumber * @NumberOfRow)