Pagination – Using OFFSET and FETCH in SQL Server 2012

OFFSET and FETCH are two new clauses introduced in SQL Server 2012 that allows us to extract a portion of rows from the result set. When we need to display a large result set to the user, the best way of going about it is to split them .i.e use pagination. In SQL Server 2012, we can achieve pagination by using the ‘OFFSET’ and ‘FETCH’ commands. Let us understand this with an example:

USE AdventureWorks2012
GO
SELECT pp.ProductId,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.productid

The query returns 504 rows as depicted above. So what do i do if i need to fetch only a portion of the above rows?

Enter OFFSET and FETCH. How? let us understand this with an example

USE AdventureWorks2012
GO
SELECT PP.ProductID,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.ProductID
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY

Here, the OFFSET tells the query to ignore the first 100 rows and then return only the following 10 rows. This is very easy to use and a quick way to return just a portion of records.
Few Limitations of the OFFSET and FETCH clause as stated in msdn:
a) ORDER BY is mandatory to use OFFSET and FETCH clause.
b) OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
c) TOP cannot be combined with OFFSET and FETCH in the same query expression.
d) The OFFSET/FETCH row count expression can be any arithmetic, constant, or parameter expression that will return an integer value. The row count expression does not support scalar sub-queries.