May 30, 2012

Pagination with OFFSET and FETCHNEXT in SQL Server 2012




Continuing my series on SQL Server 2012, today we will explore OFFSET and FETCHNEXT.

Often you may need to show only partial results to the client when the data returned is huge. In a front end application like ASP.NET, if you use the Grid, you can make use of its pagination property. However if you want to do this from server side, you need to use row_number() and supply the specific range of rows. In versions starting from 2012, there are new clauses named OFFSET and FETCH that can do paginations.

Consider the system catalog view sys.objects. Suppose you want to return first 10 rows from this view ordered by the column named ‘Name’. Use the following code

select * from sys.objects
order by name
offset 0 rows
fetch next 10 rows only

offset-fetch-next

In the above statement, offset specifies number of rows to be skipped for pagination. As it is 0, no rows will be skipped and fetching next 10 rows, will fetch 10 rows from the result set.

If you want to return the next 10 rows (rows 11 to 20), set the offset value to 10 (so that first 10 rows will be skipped and the server returns data from 11th row onwards)

select * from sys.objects
order by name
offset 10 rows
fetch next 10 rows only

offset-fetch-next-1

This way we can efficiently do pagination on the server side.

Also note that value for OFFSET should be greater than or equal to zero and value for FETCH NEXT should be greater than zero


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

9 Responses to "Pagination with OFFSET and FETCHNEXT in SQL Server 2012"
  1. starsky51 said...
    May 30, 2012 at 11:32 PM

    They seem to be adding a lot of new keywords. Wouldn't TOP 10 have done the same thing?

  2. Madhivanan said...
    May 31, 2012 at 12:25 AM

    For the above examples TOP may be enough. If you want to take rows from 11 to 20 TOP itself is not enough. You need to generate a serial number and filter it

  3. starsky51 said...
    May 31, 2012 at 2:55 AM

    Sorry. What I meant was, could TOP 10 have been used in place of FETCH NEXT 10 ROWS ONLY?
    The OFFSET keyword is a very welcome addition!

  4. Anonymous said...
    May 31, 2012 at 2:05 PM

    Great stuff, been waiting for so long for this, thanks!.

  5. Anonymous said...
    May 31, 2012 at 2:05 PM

    Greate stuff, thanks

  6. Andrei Rinea said...
    June 1, 2012 at 1:01 AM

    I understand that you're constrained to use an ORDER clause?

    What if I don't want it ordered?

  7. Madhivanan said...
    June 1, 2012 at 3:02 AM

    Andrei Rinea ,

    Pagination with specific order does not give any meaning to the resultset and thats why Order by is a costraint to use

  8. Madhivanan said...
    June 1, 2012 at 3:09 AM

    My above comment should be

    Andrei Rinea ,

    Pagination with out specific order does not give any meaning to the resultset and thats why Order by is a costraint to use

  9. Andrei Rinea said...
    June 1, 2012 at 8:06 AM

    I agree but since you CAN "select * from table" without a specific sort order I considered it could be paginated as well.

    I believe the default order is the physical layout of rows on the disk.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions