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