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, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook