Pagination in Result Set – OFFSET \ FETCH – SQL Server 2012

Today I’m going to discuss another new feature in SQL Server 2012. Pagination in result set is a new feature in SQL Server 2012. ORDER BY clause in SQL Server 2012 is enhanced with additional parameters that is OFFSET and FETCH. Using this parameter allows you to fetch data from a particular page from result set, you can also specify how many rows to retrieve. However an ORDER BY clause is compulsory to utilize this feature.

Let’s do an test, I’m going to use Adventureworks2008R2 database to run a select statement. If you don’t have this db then you can download it from http://msftdbprodsamples.codeplex.com/.

My requirement is to select employee names from ID 30 to 50. Use the query below

USE AdventureWorks2008R2
GO
SELECT BusinessEntityID,LoginID from [HumanResources].[Employee]
ORDER BY BusinessEntityID
OFFSET 30 ROWS
FETCH NEXT 20 ROWS ONLY

If you can see from the image I have used OFFSET and FETCH parameters. OFFSET lets you to skip the rows before the value you specified and FETCH will retrieve the rows after OFFSET value. FETCH is an optional parameter, if you didn’t specify FETCH then all the rows after the OFFSET value specified will be retrieved. From the example result set is sorted based on BusinessEntityID and first 30 rows are skipped and next 15 rows are retrieved.

The above result set can also be obtained by specifying BETWEEN clause in SELECT query however lets consider you don’t have an ID column.  Assume you have a requirement to collect employee salary range between 25 and 30, earlier we use RANK or ROW_NUMBER() function to sort it and then based on this value we will retrieve the data. Now you can directly get those details using OFFSET and FETCH, just do an ORDER BY on salary.

In FETCH you can either specify NEXT or FIRST both are valid. There are some points you need to consider before using it, OFFSET-FETCH clause can only be used with ORDER BY statement. TOP clause cannot be combined with OFFSET-FETCH clause. Thus to consider pagination will now be easy for programmers.