OFFSET-FETCH in SQL Server 2012 is much like LIMIT(offset, row count) in MySQL. It could be useful for ad-hoc paging purpose. It is a must to have an ORDER BY clause in the SQL statement in order to use OFFSET-FETCH.
Let us see how OFFSET-FETCH can be very important tool in action.
First let us query our fictitious table without any OFFSET-FETCH so that you could get a good picture.
Here is a simple SQL script with OFFSET-FETCH.
NEXT and FIRST key words can be used alternatively with OFFSET-FETCH to make it more readable. By the same token ROW and ROWS can be used alternatively for the same purpose.
OFFSET-FETCH WITH 0 skip acts as a TOP (N) function
FETCH keyword should always be followed by OFFSET keyword but the reverse is not true. OFFSET could come only by itself to skip a given number of rows.
Even though it is a requirement to use ORDER BY clause for OFFSET-FETCH if one prefer to select in arbitrary order it is possible to use SELECT NULL.
OFFSET-FETCH could also be used with expression which makes it very handy while dealing with dynamic SQL in stored procedure.
The above stored procedure can be used to partition data in to pages and display on interactive application or websites.
Read More: http://technet.microsoft.com/en-us/library/gg699618.aspx
