The OFFSET and FETCH Keywords in SQL Server

         A common task in a data-bound application is to display data in a page-able user interface – often some sort of data grid. For example, you might want to create a Web application that shows a list of available products, but which limits the list to ten products at a time. The user then pages through the data by clicking “Next Page” or something similar. There are many user interface design and implementation patterns that enable this kind of functionality, but they usually either involve
fetching all of the data from the database and caching it locally in the application, or implementing some sort of current page tracking functionality to adjust the query used to retrieve the data on each page.
         SQL Server 2012 introduced a new way to manage paging within the SELECT statement itself by using the new OFFSET and FETCH keywords in the ORDER BY clause to limit the query results to a specific page of data. The OFFSET keyword is used to indicate the “starting row” of the results (the number of rows to skip before this page), and the FETCH keyword is used to indicate the number of pages to be returned (the page size).
         The following code sample skips 20 rows in the underlying dataset and then returns the next 10 rows.
Using the OFFSET and FETCH keywords to return a specific page of data
         SELECT so.SalesOrderNumber, so.OrderDate, c.FirstName + ‘ ‘ + c.LastName CustomerName
         FROM SalesOrderHeader so
         JOIN Customers c ON so.CustomerKey = c.CustomerKey
         ORDER BY SalesOrderNumber ASC
         OFFSET 20 ROWS
         FETCH NEXT 10 ROWS ONLY