OFFSET and FETCH in SQL Server 2012

Introduction

SQL Server 2012 has introduced two new keywords for doing pagination of the result sets: OFFSET and FETCH. OFFSET provides a starting row from which SQL Server needs to start fetching rows and FETCH provides the number of rows we want to fetch from the result set (or in a query). OFFSET and FETCH can be used only with an order by clause.

Syntax

[ORDER BY { order by expression [ASC|DESC] } [ ,...n][OFFSET offset row count [ROW |ROWS] FETCH FIRST | NEXT fetch row count [ROW |ROWS] ONLY] ]

Offset row count: It is the number of rows to skip. This parameter must be an integer and greater than or equal to zero.

Fetch row count: It is the number of rows to return. This parameter must be an integer and greater than or equal to one.

Example

Suppose I have a table containing the yearly profits of the company. This table contains more than 50 rows. Now from my application I just want to show only 10 records at a time, so I need to use pagination.

--Create Table and Insert some dummy data.

CREATE TABLE #YearWiseBusinessData

(

Year INT,

Profit MONEY NOT NULL

)

 

DECLARE @year INT = 1960

DECLARE @profit money = 2300.00

WHILE (@year <=2013)

BEGIN

INSERT INTO #YearWiseBusinessData VALUES(@year,@profit)

SET @profit = @profit + 500.00

SET @year = @year + 1

END



Using OFFSET and FETCH clauses we can do pagination.

Skip the first 30 records and get all other records:

SELECT * FROM #YearWiseBusinessData
ORDER BY [YEAR]
OFFSET 30 ROWS

Skip the first 30 records and get the next 10 records:

SELECT * FROM #YearWiseBusinessData
ORDER BY [YEAR]
OFFSET 30 ROWS
FETCH NEXT 10 ROWS ONLY



Exception when the fetch row count is Zero.

SELECT * FROM #YearWiseBusinessData
ORDER BY [YEAR]
OFFSET 30 ROW FETCH NEXT 0 ROW ONLY



Exception when the ORDER BY clause is not defined:

SELECT * FROM #YearWiseBusinessData
OFFSET 30 ROW FETCH NEXT 0 ROW ONLY

Limitations

• OFFSET and FETCH can be used only with an order by
• OFFSET clause is mandatory with the FETCH
• The OFFSET and FETCH row count must be an integer value and it does not support sub queries
• A TOP clause cannot be used with OFFSET and FETCH

Execution Plan for OFFSET and FETCH clause



Conclusion

Using the keyword OFFSET and FETCH NEXT clauses we can get pagination easily in SQL Server 2012. This will help the developer to do pagination within a Stored Procedure (from the back end).