Windowing using OFFSET-FETCH

Last week for T-SQL Tuesday #65 the topic was learn something new and then blog about it. I looked at some of the new features in SQL Server 2012 and decided to learn about the new replacements for the FMT_ONLY only SET option. Following in that same vein, I decided to learn about the OFFSET-FETCH clause in 2012.

In SQL Server 2012, Microsoft added some additional windowing functionality. Today we will focus on how to window, or page, the results of a query using OFFSET-FETCH. So without further ado, on with the show,-er- blog.

The OFFSET-FETCH clause allows you to specify a specific number of rows to return, similar to TOP. However, unlike TOP you can also specify where the result set is to begin. Therefore using the OFFSET and FETCH keywords you can easily select a subset of your result set. This feature does require the data to be sorted in order to specify an OFFSET which may impact performance, so use with caution.

Below is the basic syntax of the OFFSET-FETCH clause. I am using the Badges table in the StackOverflow for this query and filtering on 5196.

Transact-SQL

1

2

3

4

5

6

7

8

SELECT[Id]

,[Name]

,[UserId]

,[Date]

FROM[StackOverflow].[dbo].[Badges]

WHERE[UserId]=5196;

This result set returns thirteen rows. Now, if I specify the OFFSET of zero and FETCH of ten I will get the top ten records sorted by whatever value I put in the order by clause, which in this case is date.

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

SELECT[Id]

,[Name]

,[UserId]

,[Date]

FROM[StackOverflow].[dbo].[Badges]

WHERE[UserId]=5196

ORDERBY[Date]

OFFSET10ROWS

FETCHNEXT10ROWSONLY;

A few to be aware of when using the OFFSET-FETCH clause (straight from Books Online):

ORDER BY is mandatory to use OFFSET and FETCH clause.

OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

TOP cannot be combined with OFFSET and FETCH in the same query expression.

The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.