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

offset_fetch

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.

Leave a Reply

Join the Conversation

6 Comments

  1. Pingback: DotNetShoutout
  2. I remember having to write a complicated procedure to do the same thing recently. This will be nice to use with a web app to only return a small subset. I think this will improve the speed especially when the entire result set is very large.

  3. How does this statement perform when a big offset is used? I remember this kind of statements run slow on other platforms as offset grows.

    1. @Albtotxo – Im not sure about other RDBMS platforms. However there is performance improvement in SQL Server 2012 compared to earlier versions when to do the same pagination.

    1. Gninasse — You can use ROW_NUMBER(), TOP, Order BY clause to achieve pagination however compared to OFFSET-FETCH that will degrade the performance

Leave a comment

Your email address will not be published. Required fields are marked *

*


x

Related Posts

Batch script to deploy multiple SQL files
I was working on a project where I’m supposed to deploy lots of SQL Server script files (.sql). It’s very tedious job either to manually execute ...
Performance View on INNER JOIN and OUTER JOIN
Hope you guys are getting ready for Diwali Festival :-). Before you guys start with the festival event I thought of writing an article on perform...
Synonyms - SQL Server
I was working in a project where we are using database mirroring as high availability solution. In the same instance we have multiple databases i...
powered by RelatedPosts