OFFSET and FETCH in SQL Server 2012

SQL Server 2012 has introduced two new keywords for doing pagination of the result sets: OFFSET and FETCH.

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

OFFSET and FETCH in SQL Server 2012

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

OFFSET and FETCH in SQL Server 1

Exception when the fetch row count is Zero.

OFFSET and FETCH in SQL Server 2

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

OFFSET and FETCH in SQL Server 3

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

OFFSET and FETCH in SQL Server 4

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).