OFFSET-FETCH Notes

The OFFSET-FETCH clause gives the ability to selectively fetch only a fixed window subset of the entire result set. We can also think about it as being able to paginate through the result set. This feature is very useful for UI developers who are developing a grid type of widget or page that wants to be able to paginate through the records in their grid that are being returned from the database.

The OFFSET clause does not really require the FETCH clause in order to work. However, in T-SQL a FETCH clause requires the OFFSET to be present. Also, an ORDER BY clause is mandatory if you want to use the OFFSET-FETCH clause in your query.

Here are some examples:

USE TSQL2012;
GO


-- This will skip the first 10 rows and return the remaining rows.
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY orderid DESC
OFFSET 10 ROWS;
GO

-- This will skip the first 10 rows and return only the next 10 rows
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY orderid DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

-- Example of dynamic OFFSET-FETCH filtering by passing input parameters.
-- This might be similar to a UI passing in the pagesize and pagenumber so that
-- it can paginate thru the result set. (Server-side pagination).
DECLARE
@pagesize AS BIGINT = 10, @pagenum AS BIGINT = 3;

SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY orderid DESC
OFFSET (@pagenum - 1) * @pagesize ROWS
FETCH NEXT @pagesize ROWS ONLY;

-- Example if you want to filter a number of rows in arbitrary order
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY (SELECT NULL)
OFFSET 10 ROWS;
GO