January 15, 2016

OFFSET FETCH in SQL Server 2012

I have been exploring the new OFFSET-FETCH filter introduced in SQL Server 2012 and find it quite useful. In simple words, the OFFSET and FETCH clauses give you the capability to implement a paging solution by specifying the starting and ending set of rows to return.

Madhivanan has written a nice article on OFFSET-FETCH introducing this new feature over here: Pagination with OFFSET and FETCH NEXT in SQL Server 2012

In this article, I will list down some important points to consider while working with the OFFSET-FETCH clause in SQL Server 2012
  • OFFSET indicates the number of rows to skip, FETCH indicates the rows to return after the skipped rows
  • The TOP clause in SQL Server is similar to the FETCH clause except that it does not have skipping capability.
  • However TOP supports PERCENT and WITH TIES, but OFFSET-FETCH does not.
  • Any query that uses OFFSET-FETCH must also have an ORDER BY clause
  • You can use OFFSET without FETCH, but not the opposite. With every FETCH clause, you need OFFSET clause
  • The Filter has a singular and plural support. For example to fetch one row, you can use FETCH 1 ROW. Similarly to FETCH more than 1, you can specify the plural ROWS
Check out an OFFSET-FETCH example

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

No comments: