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.


  [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


Below syntax I have refer it from MSDN.

[ ORDER BY { expression }
OFFSET { integer_const | offset_row_count_expression } { ROW | ROWS }

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

FROM Customer
OFFSET ((@PageNo - 1) * @RecordPerPage) ROWS


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;

		,RowNum = ROW_NUMBER() OVER (ORDER BY CustomerID)
	FROM Customer) result
WHERE (result.RowNum >= @FromRowNum
AND result.RowNum <= @ToRowNum)


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.


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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s