Offset & Fetch

Overview

This is the new feature introduced in SQL 2012.

OFFSET Specifies the number of rows to skip
FETCH Specifies the number of rows to return

Basically by using OFFSET & FETCH, we can get selected result in a particular order while sorting the data. So these are used along with ORDER By clause.

Usage

Here is the basic query.

SELECT TOP 5 * FROM Students ORDER BY StudentName

StudentID	StudentName	City
5	Dipa	Hyderabad
6	Kamlesh	Mumbai
8	Lalu	Hyderabad
9	Mahesh	Ahmedabad
4	Maulik	Redmond

Now, I want the result starting from 3rd row and I want to get total 4 rows starting from 3rd row, basically while sorting, I want to ignore first 2 rows, get only 4 rows and also want to ignore rest of the rows, then this is how I can achieve this

SELECT * FROM Students ORDER BY StudentName OFFSET 2 ROWS FETCH NEXT 4 ROWS ONLY

StudentID	StudentName	City
8	Lalu	Hyderabad
9	Mahesh	Ahmedabad
4	Maulik	Redmond
2	Megha	Redmond

I can also use variables instead of static value in OFFSET and FETCH

DECLARE @OFFSET INT = 2

DECLARE @FETCH INT = 3

SELECT * FROM Students ORDER BY StudentName OFFSET @OFFSET ROWS FETCH NEXT @FETCH + 1 ROWS ONLY

It will give me same result what we got earlier.

Limitation

• ORDER BY is mandatory
• OFFSET is mandatory with FETCH.. You cannot use only 1
• TOP cannot be combined with OFFSET & FETCH

OFFSET/FETCH vs ROW_NUMBER()

• First of all query is much simpler while using OFFSET/FETCH than ROW_Number, here is the comparison. Below query will give the same result with the query what we used earlier.

;WITH CTE AS

(

      SELECT      ROW_NUMBER() OVER(ORDER BY StudentName) RowNumber, *

      FROM  Students

)

SELECT      *

FROM  CTE

WHERE RowNumber BETWEEN 3 AND 6

• And if we look at the execution plan, then we can see, there is lot more going on while using ROW_Number than Offset/Fetch