SQL Server Query Pagination

This article was authored by Praveen Dabade.

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.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook