Most of us are familiar with using the TOP keyword in a T-SQL query. Basically, TOP will return a specified percentage or number of records. For instance, to see the top 100 sales orders by total price you may run a query similar to this one.

SELECT TOP 100 SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, UnitPrice, LineTotal
FROM AdventureWorks2012.Sales.SalesOrderDetail
ORDER BY LineTotal DESC;

In SQL Server 2012 there’s a new function we can use, OFFSET – FETCH. You can get the same results as above using this new functionality;

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, UnitPrice, LineTotal
FROM AdventureWorks2012.Sales.SalesOrderDetail
ORDER BY LineTotal DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

When you use OFFSET you specify the starting position. To begin at the top (or bottom you start at 0, which I did above. But you can start anywhere. To see the next hundred records you’d say OFFSET 99. You can use a variable for OFFSET, but OFFSET must be an integer that is 0 or greater.

FETCH NEXT is the number of records to return. In my case I’m only returning 100 rows but you can specify any valid number. It can come from a variable or a subquery