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