Pagination of Results in SQL Server In Efficient Way

Pagination of results from a select statement is vital in SQL server programming. Nowadays web apps are becoming the preferred interface even for enterprise application. Delivering only the least required data to the user interface is one among the vital criteria for improving the performance of web app or even client-server application. Pagination will help you to retrieve the minimum required rows from a select statement to be displayed in the user interface. Only when the user needs to see more data, they can navigate to the further pages of result reducing the load on the server as well as improving the speed of the user interface. Here are the pagination methods, I’ve used so far.

Pagination of Results in SQL Server 2012 and Higher (2014, 2016, 2017, …)

In SQL Server 2012 and higher versions you can use the OFFSET-FETCH clause to fetch only a part or page of result returned by the select query. As an input you have to give the number of rows per page and the page number you want to retrieve. For this first you have to find the number of records in the result set. Form that you can find the number of pages and the page number you want to retrieve. Here is a simple example to do pagination of results in SQL server for a select statement SELECT * FROM Sales.Orders ORDER BY OrderID.

Step 1: Identify the number of records & pages in the result set

Using count() you can get the Number of records in the result set and dividing the COUNT() by the expected number of records in a page will give you the total number of pages you can split the result set with. For example if you want the number of records in a page to be 10, then:

Use WideWorldImporters
GO
DECLARE @RowsPerPage Numeric(3,1) = 10
SELECT 
	count(*) As 'Number Of Rows', 
	CEILING(count(*)/@RowsPerPage) As 'Number of pages' 
	FROM Sales.Orders

/* Result */
Number Of Rows Number of pages
-------------- ----------------
73595          7360
(1 row affected)

Step 2: Retrieve a specific page of result.

After identifying the total number of pages available in the result set, you can use the below query to fetch the exact page from the result set using the OFFSET – FETCH clause.

Use WideWorldImporters
GO

/* To get first page of result */
DECLARE @RowsPerPage INT = 10, 
	@PageNumber INT = 1
SELECT * FROM Sales.Orders ORDER BY OrderID
	OFFSET (@PageNumber-1)*@RowsPerPage ROWS 
	FETCH NEXT @RowsPerPage ROWS ONLY
GO

/* To get 25th page */
DECLARE @RowsPerPage INT = 10, 
	@PageNumber INT = 25
SELECT * FROM Sales.Orders ORDER BY OrderID
	OFFSET (@PageNumber-1)*@RowsPerPage ROWS 
	FETCH NEXT @RowsPerPage ROWS ONLY
GO

/* To get the Last page */
DECLARE @RowsPerPage INT = 10, 
	@PageNumber INT = 7360
SELECT * FROM Sales.Orders ORDER BY OrderID
	OFFSET (@PageNumber-1)*@RowsPerPage ROWS 
	FETCH NEXT @RowsPerPage ROWS ONLY
GO

Pagination in SQL Server 2005 & 2008

SQL Server versions older then 2012 does not have OFFSET-FETCH class. So you have to use the ROW_NUMBER() method to navigate through the range of rows. Here is a simple example of pagination of statement SELECT * FROM Sales.Orders ORDER BY OrderID on SQL server 2005, 2008 and 2008 R2.

Step 1: Identify the number of records in the result set

The first thing you have to do is to find the total number of rows. Use the COUNT() to get the number of rows in the result set.

Use WideWorldImporters
GO
SELECT 
	count(*) As 'Number Of Rows'
	FROM Sales.Orders

Step 2: Retrieve a specific range of rows from the result

Then using the number of records you can navigate through the range of rows and retrieve them alone from the result set. For example, if you want to fetch only the 10 rows from row number 101, the statement will be like this:

DECLARE @BeginRowNo INT = 101, 
	@EndRowNo INT = 110
SELECT * FROM ( 
	SELECT 
		ROW_NUMBER() OVER ( ORDER BY OrderID ) AS RowNum, *
		FROM Sales.Orders
	) AS ResultSet
WHERE
	ResultSet.RowNum &amp;amp;gt;= @BeginRowNo AND
	ResultSet.RowNum &amp;amp;lt;= @EndRowNo
ORDER BY ResultSet.OrderID

Reference

Ways to paginate results in SQL Server @ stackoverflow.