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


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

9 comments:

starsky51 said...

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

Madhivanan said...

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

starsky51 said...

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!

Anonymous said...

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

Anonymous said...

Greate stuff, thanks

Andrei Rinea said...

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

What if I don't want it ordered?

Madhivanan said...

Andrei Rinea ,

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

Madhivanan said...

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

Andrei Rinea said...

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.