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.


Posted

in

by

Comments

6 responses to “Pagination in Result Set – OFFSET \ FETCH – SQL Server 2012”

  1. SQL-Articles » Pagination in Result Set – OFFSET \ FETCH – SQL Server 2012…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Ben avatar

    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. albTotxo avatar
    albTotxo

    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. VidhyaSagar avatar
      VidhyaSagar

      @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.

  4. gninasse ibrahim avatar
    gninasse ibrahim

    how to use pagination in sql server 2008 and 2005?

    1. VidhyaSagar avatar
      VidhyaSagar

      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 Reply

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