This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

SQL Server 2012 T-SQL at a Glance – OFFSET/FETCH

Early this month during the week of SQL PASS Seattle 2010 Microsoft announced the first public community technology preview (CTP) of Microsoft SQL Server 2012 (formerly code-named "Denali"), and made it publicly available for download. As far as T-SQL is concerned, a few cool enhancements are already implemented in CTP1, and I’m still hoping very much to see a more complete implementation of window functions in a future CTP, and of course, in the final release. You can find documentation about the new T-SQL features in Denali CTP1 online.

During the year 2011 I’m going to provide in-depth coverage of the T-SQL features in Denali. For now, I’m going to provide a series of brief entries with a preview of the new features to get you started playing with those. This entry’s focus is a new option called OFFSET/FETCH designed for ad-hoc paging purposes.

To achieve ad-hoc paging prior to SQL Server Denali you could use a couple of querying elements: TOP and ROW_NUMBER. But with both elements there are limitations. The TOP option allows limiting the number of rows but it doesn’t have a skipping capability (e.g., skip 10 rows and return the next 10 rows only). Furthermore, the TOP option is not an ISO and ANSI SQL standard feature but rather a proprietary feature in SQL Server.

The ROW_NUMBER function does allow you to request the exact range of rows to filter (e.g., rows with row numbers 11 through 20), and it is also standard; however, you cannot refer to window functions in the WHERE clause of a query. The workaround is to use a table expression such as a CTE, like so:

The extra layer of the CTE adds complication to the code affecting its readability and maintainability.

SQL Server Denali introduces a new filtering option called OFFSET/FETCH which you can think of as an extension to the ORDER BY clause. Right after the query’s ORDER BY clause you specify the OFFSET clause (mandatory) with however many rows you wish to skip (zero for none); then you specify the FETCH clause with however many rows you wish to filter. Here are a few examples achieving the same functionality that the previews queries did:

As you can see, the OFFSET/FETCH option is very intuitive and English-like. In fact, you can interchange the words ROW | ROWS if you like (e.g., when you need to skip or fetch only one row you can use ROW instead of ROWS), and you can also interchange the words FIRST|NEXT as you wish, allowing for intuitive writing.

Compared to TOP, the OFFSET/FETCH option has two main advantages: it is standard and it does support a skipping option. Concerning the standard, if you look at the SQL:2008 standard you will not find the complete OFFSET/FETCH option, but rather a more limited option with just a FETCH clause (called fetch) . Microsoft implemented the more flexible option including both the OFFSET and FETCH clauses based on drafts they are using of the SQL:2011 standard, which is not out yet. Compared to ROW_NUMBER, the OFFSET/FETCH option has the advantage that you don’t need the extra layer of the table expression, making the code more readable, and hence easier to maintain.

A very important thing to understand about the OFFSET/FETCH option is that you are allowed to use it along with an ORDER BY clause in the inner query of a table expression (view, inline table function, derived table, CTE); however, just like with TOP, an outer query against the table expression has no presentation ordering guarantees unless it also has an ORDER BY clause.

This was just a glimpse to the OFFSET/FETCH option to get you started, and I’ll continue with such brief previews of the other new T-SQL features in Denali. There’s much more to say about OFFSET/FETCH and the other features both in terms of functionality and optimization. And as mentioned, next year I’m going to provide in-depth coverage of each of the features.

In the meanwhile, I have a couple of small challenges for you:

Puzzle 1: Nondeterministic OFFSET/FETCH (without ordering)

SQL Server supports a TOP without an ORDER BY clause. For example, to return an arbitrary row from a table, you can use a TOP query without an ORDER BY like so: