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.

Related: Denali T-SQL at a Glance: New and Enhanced Functions

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:

USE AdventureWorks2008R2;-- page 1WITH C AS(   SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, SalesOrderID) AS rownum,    SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal  FROM Sales.SalesOrderHeader)SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotalFROM CWHERE rownum BETWEEN 1 AND 10ORDER BY OrderDate, SalesOrderID;SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal------------ ----------------------- ----------- ------------- ---------------------43659        2005-07-01 00:00:00.000 29825       279           20565.620643660        2005-07-01 00:00:00.000 29672       279           1294.252943661        2005-07-01 00:00:00.000 29734       282           32726.478643662        2005-07-01 00:00:00.000 29994       282           288 32.528943663        2005-07-01 00:00:00.000 29565       276           419.458943664        2005-07-01 00:00:00.000 29898       280           24432.608843665        2005-07-01 00:00:00.000 29580       283           14352.771343666        2005-07-01 00:00:00.000 30052       276           5056.489643667        2005-07-01 00:00:00.000 29974       277           6107.08243668        2005-07-01 00:00:00.000 29614       282           35944.1562-- page 2WITH C AS(   SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, SalesOrderID) AS rownum,    SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal  FROM Sales.SalesOrderHeader)SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotalFROM CWHERE rownum BETWEEN 11 AND 20ORDER BY OrderDate, SalesOrderID;SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal------------ ----------------------- ----------- ------------- ---------------------43669        2005-07-01 00:00:00.000 29747       283           714.704343670        2005-07-01 00:00:00.000 29566       275           6122.08243671        2005-07-01 00:00:00.000 29890       283           8128.787643672        2005-07-01 00:00:00.000 30067       282           6124.18243673        2005-07-01 00:00:00.000 29844       275           3746.201543674        2005-07-01 00:00:00.000 29596       282           2624.38243675        2005-07-01 00:00:00.000 29827       277           5716.310243676        2005-07-01 00:00:00.000 29811       275           14203.45843677        2005-07-01 00:00:00.000 29824       278           7793.110843678        2005-07-01 00:00:00.000 29889       281           9799.9243-- parameterizedDECLARE   @pagenum  AS INT = 2,  @pagesize AS INT = 10;WITH C AS(   SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, SalesOrderID) AS rownum,    SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal  FROM Sales.SalesOrderHeader)SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotalFROM CWHERE rownum BETWEEN (@pagenum - 1) * @pagesize + 1 AND @pagenum * @pagesizeORDER BY OrderDate, SalesOrderID;

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:

-- page 1SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotalFROM Sales.SalesOrderHeaderORDER BY OrderDate, SalesOrderIDOFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal------------ ----------------------- ----------- ------------- ---------------------43659        2005-07-01 00:00:00.000 29825       279           20565.620643660        2005-07-01 00:00:00.000 29672       279           1294.252943661        2005-07-01 00:00:00.000 29734       282           32726.478643662        2005-07-01 00:00:00.000 29994       282           28832.528943663        2005-07-01 00:00:00.000 29565       276           419.458943664        2005-07-01 00:00:00.000 29898       280           24432.608843665        2005-07-01 00:00:00.000 29580       283           14352.771343666        2005-07-01 00:00:00.000 30052       276           5056.489643667        2005-07-01 00:00:00.000 29974       277           6107.08243668        2005-07-01 00:00:00.000 29614       282           35944.1562-- page 2SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotalFROM Sales.SalesOrderHeaderORDER BY OrderDate, SalesOrderIDOFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal------------ ----------------------- ----------- ------------- ---------------------43669        2005-07-01 00:00:00.000 29747       283           714.704343670        2005-07-01 00:00:00.000 29566       275           6122.08243671        2005-07-01 00:00:00.000 29890       283           8128.787643672        2005-07-01 00:00:00.000 30067       282           6124.18243673        2005-07-01 00:00:00.000 29844       275           3746.201543674        2005-07-01 00:00:00.000 29596       282           2624.38243675        2005-07-01 00:00:00.000 29827       277           5716.310243676        2005-07-01 00:00:00.000 29811       275           14203.45843677        2005-07-01 00:00:00.000 29824       278           7793.110843678        2005-07-01 00:00:00.000 29889       281           9799.9243-- parameterizedDECLARE   @pagenum  AS INT = 2,  @pagesize AS INT = 10;SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotalFROM Sales.SalesOrderHeaderORDER BY OrderDate, SalesOrderIDOFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY; 

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:

SELECT TOP (1) SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotalFROM Sales.SalesOrderHeader;Try the same with OFFSET/FETCH:SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotalFROM Sales.SalesOrderHeaderOFFSET 0 ROWS FETCH FIRST 1 ROW ONLY; 

And you will get an error:

Msg 102, Level 15, State 1, Line 3Incorrect syntax near '0'.Msg 153, Level 15, State 2, Line 3Invalid usage of the option FIRST in the FETCH statement. 

Can you think of a workaround such that the plan for the query would neither involve a sort operation, nor an index scan with Ordered:True?

Puzzle 2: OFFSET/FETCH with an empty set

SQL Server allows you to use TOP with zero rows as input to return an empty set, e.g.,

SELECT TOP (0) SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotalFROM Sales.SalesOrderHeaderORDER BY OrderDate, SalesOrderID; 

Try a similar query with OFFSET/FETCH:

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotalFROM Sales.SalesOrderHeaderORDER BY OrderDate, SalesOrderIDOFFSET 0 ROWS FETCH FIRST 0 ROWS ONLY; 

And you will get the following error:

Msg 10744, Level 15, State 1, Line 4The number of rows provided for a FETCH clause must be greater then zero. 

This behavior is standard, BTW. Can you think of a workaround?

Cheers,

BG