SQL Server 2012 Query Pagination

In SQL Server 2012, Microsoft introduced a couple of T-SQL Enhancements. One of them is Query Pagination which we will explore today.

For this demonstration, I am using the Northwind database to demonstrate this new feature. Now in most of the applications, a common requirement is how to fetch the data from the database servers, page wise.
In earlier versions of SQL Server like SQL Server 2005/2008/R2, we can implement Pagination by using different techniques. For example, we implement pagination using ROW_NUMBER() function or CTE - Common Table Expression.

In SQL Server 2012, Microsoft has introduced Pagination as a part of Select query in a Order By clause. Now you will have to use OFFSET and FETCH NEXT with the order by clause.

Let's take a look at a few examples. I am using Northwind database for this demonstration. I have created a Stored Procedure which takes two parameters. First parameter takes the page number and the second parameter ask you to fetch the no. of records for that page. The stored procedure code is as below -



If you execute the above stored procedure

EXEC FetchPagedRecords 2,10

you will get the following results -



The OFFSET specifies the number of rows to skip before it starts returning the rows and FETCH NEXT specifies the number of rows to be returned.

Microsoft has introduced an easy way of implementing Data Paging in SQL Server 2012 by adding OFFSET and FETCH NEXT in an Order By clause. I hope you will use it in your applications.