SQL Tip – An alternative way to page results

An alternative way to page results using OFFSET AND FETCH.

Database used in this example is Adventure Works for SQL Server 2014. You can download the database here.

This stored procedure will return paged results from the HumanResources.Employee table.

CREATE PROCEDURE [dbo].[GetPagedEmployees]
       @pageNumber int = 1, -- default only
       @pageSize int = 10 -- default only
AS
BEGIN

	SELECT [BusinessEntityID]
		  ,[NationalIDNumber]
		  ,[LoginID]
		  ,[OrganizationNode]
		  ,[OrganizationLevel]
		  ,[JobTitle]
		  ,[BirthDate]
		  ,[MaritalStatus]
		  ,[Gender]
		  ,[HireDate]
		  ,[SalariedFlag]
		  ,[VacationHours]
		  ,[SickLeaveHours]
		  ,[CurrentFlag]
		  ,[rowguid]
		  ,[ModifiedDate]
	  FROM [AdventureWorks2014].[HumanResources].[Employee]
	  ORDER BY [BusinessEntityID]
	  OFFSET ((@pageNumber - 1)  * @pageSize) ROWS
	  FETCH NEXT @pageSize ROWS ONLY
END

Execution results: