SQL Server 2012-Offset

When we viewing a large data set in a report the data is split across pages. This is also called pagination where a certain set of rows are displayed for the user. There are lot of techniques available to achieve such a functionality. In SQL Server 2012 there is a feature in T-SQL that would enable the user to achieve such a functionality. The feature is called OFFSET and FETCH, this allows users to build paging applications. Let me explain the use of OFFSET and FETCH with the following Example:

SELECT OrderDetailID
ProductID,
OrderDate,
Quantity,
ActualCost
FROM Products
ORDER BY OrderDate
OFFSET 0 ROWS
FETCH NEXT 15 ROWS ONLY;

In the above Select statement, the OFFSET provides a starting row from which to display the data, FETCH provides the number of rows to displayed in a page/result set. The above statement would be for say the first page of the result set. In order to view say the next page, the OFFSET value would change from 0 to 15, for the next page it would change from 15 to 30. Now in order to make this select more general for paging, the select statement would be modified to the following:


SELECT OrderDetailID
ProductID,
OrderDate,
Quantity,
ActualCost
FROM Products
ORDER BY OrderDate
OFFSET @OffSet ROWS
FETCH NEXT 15 ROWS ONLY;

The Value of a variable can be controlled through a function or a table for the application, like as when the user hits the next page the value being passed to the offset variable will change. Like how variables are being used for OFFSET, the FETCH clause can also take variables. For example:

SELECT OrderDetailID

ProductID,
OrderDate,
Quantity,
ActualCost
FROM Products
ORDER BY OrderDate
OFFSET @OffSet ROWS
FETCH NEXT @FetchRows - @Offset + 1 ROWS ONLY;