OFFSET and FETCH are two new clauses introduced in SQL erver 2012. Used together in your queries,
they allow you to extract a portion of rows from your result set.
We will look at typical SELECT
query which you can run against the AdventureWorks
database and add in ORDER BY
clause.
SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM [Person].[Person] ORDER BY BusinessEntityID
The following result set would be returned:
The query returns over 19,000 rows. However, what if you wanted to retrieve just a selection of rows from within that result set and using the SELECT TOP
statement doesn't deliver the record you need? In comes the combination of OFFSET
and FETCH
. Take a look at the following query:
SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM [Person].[Person] ORDER BY BusinessEntityID OFFSET 100 ROWS FETCH NEXT 5 ROWS ONLY
Here, the OFFSET
tells the query to ignore the first 100 rows and then return only the following five rows. This is very easy to...