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 1
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, SubTotal
FROM C
WHERE rownum BETWEEN 1 AND 10
ORDER BY OrderDate, SalesOrderID;

SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal
------------ ----------------------- ----------- ------------- ---------------------
43659        2005-07-01 00:00:00.000 29825       279           20565.6206
43660        2005-07-01 00:00:00.000 29672       279           1294.2529
43661        2005-07-01 00:00:00.000 29734       282           32726.4786
43662        2005-07-01 00:00:00.000 29994       282           288 32.5289
43663        2005-07-01 00:00:00.000 29565       276           419.4589
43664        2005-07-01 00:00:00.000 29898       280           24432.6088
43665        2005-07-01 00:00:00.000 29580       283           14352.7713
43666        2005-07-01 00:00:00.000 30052       276           5056.4896
43667        2005-07-01 00:00:00.000 29974       277           6107.082
43668        2005-07-01 00:00:00.000 29614       282           35944.1562

-- page 2
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, SubTotal
FROM C
WHERE rownum BETWEEN 11 AND 20
ORDER BY OrderDate, SalesOrderID;

SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal
------------ ----------------------- ----------- ------------- ---------------------
43669        2005-07-01 00:00:00.000 29747       283           714.7043
43670        2005-07-01 00:00:00.000 29566       275           6122.082
43671        2005-07-01 00:00:00.000 29890       283           8128.7876
43672        2005-07-01 00:00:00.000 30067       282           6124.182
43673        2005-07-01 00:00:00.000 29844       275           3746.2015
43674        2005-07-01 00:00:00.000 29596       282           2624.382
43675        2005-07-01 00:00:00.000 29827       277           5716.3102
43676        2005-07-01 00:00:00.000 29811       275           14203.458
43677        2005-07-01 00:00:00.000 29824       278           7793.1108
43678        2005-07-01 00:00:00.000 29889       281           9799.9243

-- parameterized
DECLARE
  @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, SubTotal
FROM C
WHERE 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 1
SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal
------------ ----------------------- ----------- ------------- ---------------------
43659        2005-07-01 00:00:00.000 29825       279           20565.6206
43660        2005-07-01 00:00:00.000 29672       279           1294.2529
43661        2005-07-01 00:00:00.000 29734       282           32726.4786
43662        2005-07-01 00:00:00.000 29994       282           28832.5289
43663        2005-07-01 00:00:00.000 29565       276           419.4589
43664        2005-07-01 00:00:00.000 29898       280           24432.6088
43665        2005-07-01 00:00:00.000 29580       283           14352.7713
43666        2005-07-01 00:00:00.000 30052       276           5056.4896
43667        2005-07-01 00:00:00.000 29974       277           6107.082
43668        2005-07-01 00:00:00.000 29614       282           35944.1562

-- page 2
SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal
------------ ----------------------- ----------- ------------- ---------------------
43669        2005-07-01 00:00:00.000 29747       283           714.7043
43670        2005-07-01 00:00:00.000 29566       275           6122.082
43671        2005-07-01 00:00:00.000 29890       283           8128.7876
43672        2005-07-01 00:00:00.000 30067       282           6124.182
43673        2005-07-01 00:00:00.000 29844       275           3746.2015
43674        2005-07-01 00:00:00.000 29596       282           2624.382
43675        2005-07-01 00:00:00.000 29827       277           5716.3102
43676        2005-07-01 00:00:00.000 29811       275           14203.458
43677        2005-07-01 00:00:00.000 29824       278           7793.1108
43678        2005-07-01 00:00:00.000 29889       281           9799.9243

-- parameterized
DECLARE
  @pagenum  AS INT = 2,
  @pagesize AS INT = 10;

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
OFFSET (@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, SubTotal
FROM Sales.SalesOrderHeader;

Try the same with OFFSET/FETCH:

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY;

And you will get an error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '0'.
Msg 153, Level 15, State 2, Line 3
Invalid 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, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;

Try a similar query with OFFSET/FETCH:

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
OFFSET 0 ROWS FETCH FIRST 0 ROWS ONLY;

And you will get the following error:

Msg 10744, Level 15, State 1, Line 4
The 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

Discuss this Blog Entry 6

on Nov 23, 2010
Not terribly elegant, but it works up max BIGINT for the PK

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
OFFSET 9223372036854775807 ROWS FETCH FIRST 1 ROWS ONLY;






on Nov 21, 2010
For 1st puzzle, i think we can use your prev. trick of (select 1) only..
SELECT * from tblNumbers order by (select 1) offset 0 rows FETCH First 1 ROW ONLY;

For 2nd : We can take a variable and set its value to 0 and use that variable in FETCH clause.
declare @I int = 0
SELECT * from tblNumbers order by (select 1) offset 0 rows FETCH First @I ROW ONLY;




on Nov 25, 2010
Hi all,
In Regan's solution I would only remove OrderDate and SalesOrderID from ORDER BY clause since we don't expect anything in the resultset:

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH FIRST (SELECT 0) ROWS ONLY;





on Nov 23, 2010
This is better

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
OFFSET 0 ROWS FETCH FIRST (SELECT 0) ROWS ONLY;










on Nov 23, 2010
Hi Regan,

Well done! I was aiming at your second solution: FETCH FIRST (SELECT 0) ROWS.

on Nov 21, 2010
Hi ddRamiReddy,

Regarding #1, that's what I had in mind. :)
Regarding #2, your solution is correct, but there's a simpler option that doesn't require a variable.




Please or Register to post comments.

What's Puzzled By T-SQL Blog?

T-SQL tips and logical puzzles from Itzik Ben-Gan.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×