SQL Pagination using OFFSET / FETCH NEXT Clause in SQL Server 2012

Well, I think we as a developer implemented a pagination in many application. But implement pagination with millions of records by keeping performance in mind always be a headache for developers. So we always tend to look for better approach and technique. From SQL Server 2012 sql server team at Microsoft comes with a lot of t-sql enhancement and OFFSET…FETCH NEXT clause is one of them which is used to implement pagination at sql server side in a very simple and elegant way. This feature is also appears to be less expensinve from the performance point of view than the previous technique we were using. We have recently upgraded one of our application database from SQL Server 2008 R2 to SQL Server 2014. Before SQL Server 2014, we were using Derived table with ROW_NUMBER() Function. In this post I’ll show you both the technique.

First let’s take a look at OFFSET…FETCH NEXT clause in action.

Create Schema

Let’s create a table with 3 millions of records.

SET NOCOUNT ON;

CREATE TABLE Customer
(
  [CustomerID] [int] IDENTITY(1,1) NOT NULL,
  FirstName AS ('First' + CAST(CustomerID AS VARCHAR)),
  LastName AS ('Last' + CAST(CustomerID AS VARCHAR)),
  MobileNo VARCHAR(20),
  LandLineNo VARCHAR(20),
  City VARCHAR(20),
  State VARCHAR(20),
  Zip VARCHAR(20)
)

INSERT INTO Customer VALUES('7625045789', '261-754-28354', 'NW', 'NW', '6592626')
GO 3000000

Syntax

Below syntax I have refer it from MSDN.

[ ORDER BY { expression }
OFFSET { integer_const | offset_row_count_expression } { ROW | ROWS }
FETCH { FIRST|NEXT } { ROW|ROWS } ONLY ]

Now, Let’s understand each part.

OFFSET { integer_const | offset_row_count_expression } { ROW | ROWS } :  It specifies the number of rows to skip.

FETCH { FIRST|NEXT } { ROW|ROWS } ONLY  :  It specifies the number of rows to return.

This clause is something like ready made option for paging. Very easy to implement and understand. See the below script where we see the records of page no 91 with page size of 15.

DECLARE @PageNo INT = 91, @RecordPerPage INT = 15

SELECT
	CustomerID
	,FirstName
	,LastName
	,MobileNo
	,LandLineNo
	,City
	,State
	,Zip
FROM Customer
ORDER BY CustomerID
OFFSET ((@PageNo - 1) * @RecordPerPage) ROWS
FETCH NEXT @RecordPerPage ROWS ONLY

Paging_With_Offset

Though this clause have some syntactic limitation like –

  • OFFSET clause is mandatory with FETCH. You cannot directly use ORDER BY…FETCH.
  • You cannot be combined TOP clause with OFFSET and FETCH in the same query.
  • To use OFFSET…FETCH NEXT clause ORDER BY is compulsory.

In SQL Server 2008, we have implemented same paging functionality using ROW_NUMBER() Function like this –

DECLARE @PageNo INT = 91,
@RecordPerPage INT = 15,
@FromRowNum INT,
@ToRowNum INT

SET @FromRowNum = ((@PageNo - 1) * @RecordPerPage + 1);

SET @ToRowNum = @PageNo * @RecordPerPage;

SELECT
	*
FROM (SELECT
		CustomerID
		,FirstName
		,LastName
		,MobileNo
		,LandLineNo
		,City
		,State
		,Zip
		,RowNum = ROW_NUMBER() OVER (ORDER BY CustomerID)
	FROM Customer) result
WHERE (result.RowNum >= @FromRowNum
AND result.RowNum <= @ToRowNum)

Paging_With_RowNumber

All-right both the query return the same result. But how OFFSET…FETCH NEXT clause is less expensive than above script which uses ROW_NUMBER() Function. Let’s take a look at execution plan of both the query.

Offset_ExecutionPlanRowNumber_ExecutionPlan

Please take a note that the execution plan shown in above image may differ as per the environment. I have tested it in my PC with 3 million records. So before applying it to your production server, please test it with more records.

That’s it guys. Hope you enjoyed this post. Thank you for your time.