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