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:

  1. USE AdventureWorks2008R2;
  2.  
  3. -- page 1
  4. WITH C AS
  5. (
  6.   SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, SalesOrderID) AS rownum,
  7.     SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  8.   FROM Sales.SalesOrderHeader
  9. )
  10. SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  11. FROM C
  12. WHERE rownum BETWEEN 1 AND 10
  13. ORDER BY OrderDate, SalesOrderID;
  14.  
  15. SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal
  16. ------------ ----------------------- ----------- ------------- ---------------------
  17. 43659        2005-07-01 00:00:00.000 29825       279           20565.6206
  18. 43660        2005-07-01 00:00:00.000 29672       279           1294.2529
  19. 43661        2005-07-01 00:00:00.000 29734       282           32726.4786
  20. 43662        2005-07-01 00:00:00.000 29994       282           288 32.5289
  21. 43663        2005-07-01 00:00:00.000 29565       276           419.4589
  22. 43664        2005-07-01 00:00:00.000 29898       280           24432.6088
  23. 43665        2005-07-01 00:00:00.000 29580       283           14352.7713
  24. 43666        2005-07-01 00:00:00.000 30052       276           5056.4896
  25. 43667        2005-07-01 00:00:00.000 29974       277           6107.082
  26. 43668        2005-07-01 00:00:00.000 29614       282           35944.1562
  27.  
  28. -- page 2
  29. WITH C AS
  30. (
  31.   SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, SalesOrderID) AS rownum,
  32.     SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  33.   FROM Sales.SalesOrderHeader
  34. )
  35. SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  36. FROM C
  37. WHERE rownum BETWEEN 11 AND 20
  38. ORDER BY OrderDate, SalesOrderID;
  39.  
  40. SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal
  41. ------------ ----------------------- ----------- ------------- ---------------------
  42. 43669        2005-07-01 00:00:00.000 29747       283           714.7043
  43. 43670        2005-07-01 00:00:00.000 29566       275           6122.082
  44. 43671        2005-07-01 00:00:00.000 29890       283           8128.7876
  45. 43672        2005-07-01 00:00:00.000 30067       282           6124.182
  46. 43673        2005-07-01 00:00:00.000 29844       275           3746.2015
  47. 43674        2005-07-01 00:00:00.000 29596       282           2624.382
  48. 43675        2005-07-01 00:00:00.000 29827       277           5716.3102
  49. 43676        2005-07-01 00:00:00.000 29811       275           14203.458
  50. 43677        2005-07-01 00:00:00.000 29824       278           7793.1108
  51. 43678        2005-07-01 00:00:00.000 29889       281           9799.9243
  52.  
  53. -- parameterized
  54. DECLARE
  55.   @pagenum  AS INT = 2,
  56.   @pagesize AS INT = 10;
  57.  
  58. WITH C AS
  59. (
  60.   SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, SalesOrderID) AS rownum,
  61.     SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  62.   FROM Sales.SalesOrderHeader
  63. )
  64. SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  65. FROM C
  66. 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:

  1. -- page 1
  2. SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  3. FROM Sales.SalesOrderHeader
  4. ORDER BY OrderDate, SalesOrderID
  5. OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
  6.  
  7. SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal
  8. ------------ ----------------------- ----------- ------------- ---------------------
  9. 43659        2005-07-01 00:00:00.000 29825       279           20565.6206
  10. 43660        2005-07-01 00:00:00.000 29672       279           1294.2529
  11. 43661        2005-07-01 00:00:00.000 29734       282           32726.4786
  12. 43662        2005-07-01 00:00:00.000 29994       282           28832.5289
  13. 43663        2005-07-01 00:00:00.000 29565       276           419.4589
  14. 43664        2005-07-01 00:00:00.000 29898       280           24432.6088
  15. 43665        2005-07-01 00:00:00.000 29580       283           14352.7713
  16. 43666        2005-07-01 00:00:00.000 30052       276           5056.4896
  17. 43667        2005-07-01 00:00:00.000 29974       277           6107.082
  18. 43668        2005-07-01 00:00:00.000 29614       282           35944.1562
  19.  
  20. -- page 2
  21. SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  22. FROM Sales.SalesOrderHeader
  23. ORDER BY OrderDate, SalesOrderID
  24. OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
  25.  
  26. SalesOrderID OrderDate               CustomerID  SalesPersonID SubTotal
  27. ------------ ----------------------- ----------- ------------- ---------------------
  28. 43669        2005-07-01 00:00:00.000 29747       283           714.7043
  29. 43670        2005-07-01 00:00:00.000 29566       275           6122.082
  30. 43671        2005-07-01 00:00:00.000 29890       283           8128.7876
  31. 43672        2005-07-01 00:00:00.000 30067       282           6124.182
  32. 43673        2005-07-01 00:00:00.000 29844       275           3746.2015
  33. 43674        2005-07-01 00:00:00.000 29596       282           2624.382
  34. 43675        2005-07-01 00:00:00.000 29827       277           5716.3102
  35. 43676        2005-07-01 00:00:00.000 29811       275           14203.458
  36. 43677        2005-07-01 00:00:00.000 29824       278           7793.1108
  37. 43678        2005-07-01 00:00:00.000 29889       281           9799.9243
  38.  
  39. -- parameterized
  40. DECLARE
  41.   @pagenum  AS INT = 2,
  42.   @pagesize AS INT = 10;
  43.  
  44. SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  45. FROM Sales.SalesOrderHeader
  46. ORDER BY OrderDate, SalesOrderID
  47. 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:

  1. SELECT TOP (1) SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  2. FROM Sales.SalesOrderHeader;
  3.  
  4. Try the same with OFFSET/FETCH:
  5.  
  6. SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  7. FROM Sales.SalesOrderHeader
  8. OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY;

And you will get an error:

  1. Msg 102, Level 15, State 1, Line 3
  2. Incorrect syntax near '0'.
  3. Msg 153, Level 15, State 2, Line 3
  4. 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.,

  1. SELECT TOP (0) SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
  2. FROM Sales.SalesOrderHeader
  3. ORDER BY OrderDate, SalesOrderID;

Try a similar query with OFFSET/FETCH:

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

And you will get the following error:

  1. Msg 10744, Level 15, State 1, Line 4
  2. 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) ×