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.
- How to use ‘WITH RESULT SETS’ in SQL Server 2012 (appliedsql.wordpress.com)
- IIF – The InLine conditional Statement in SQL Server 2012 (appliedsql.wordpress.com)
- CONCAT() function in SQL Server 2012 (appliedsql.wordpress.com)