Last month I started a two-part series about the OFFSET/FETCH filter that’s introduced in the next version of SQL Server, code-named Denali. I described the fundamentals of the feature, including its syntax and optimization. This month I cover additional aspects of OFFSET/FETCH, such as the use of the filter in table expressions, logical query processing, modifications, determinism, and randomization. You can use the code in Web Listing 1 (www.sqlmag.com, InstantDoc ID 129202) to create the sample data used in this article. This is the same sample data used last month, so if you already created it, you don’t need to run the code again.
OFFSET/FETCH and Table Expressions
I mentioned last month (“OFFSET/FETCH, Part 1”) that there are some similarities between the conceptual designs of the OFFSET/FETCH filter and the TOP filter. One of those similarities is that both filters are connected to the ORDER BY clause that traditionally served a presentation ordering meaning. The upside regarding this similarity is that if you worked with TOP, you will very quickly get the hang of working with OFFSET/FETCH. The downside is that the confusing aspects of the TOP design that are related to ordering are also applicable to OFFSET/FETCH.
You might have stumbled into cases in the past in which an attempt to define a view (or any other type of table expression for that matter) was rejected by SQL Server when an ORDER BY clause was specified in the query. For example, the following isn’t allowed:
CREATE VIEW dbo.MyViewAS
SELECT orderid, orderdate, custid, filler
FROM dbo.Orders
ORDER BY orderdate DESC, orderid DESC;
GO
The reason for this restriction stems from the relational model that’s the foundation of SQL, which in turn is the foundation of T-SQL. A view in SQL is supposed to represent a table; a table is an attempt (never mind how successful) at representing a relation from the relational model. A relation is supposed to represent a set from mathematical set theory. A set has no order. So as you can see, an ordered view is by definition a concept that contradicts the relational model, and therefore as a rule, both standard SQL and SQL Server disallow ORDER BY in a query that defines a table expression.
To allow TOP queries in a table expression definition, SQL Server makes an exception to this rule when TOP is also specified. But here’s the tricky part that many people fail to realize: The ordering is guaranteed only for the immediate level that contains the ORDER BY clause—not for an outer level. In other words, TOP will perform the correct filtering based on the specified ORDER BY clause, but the outer query against the table expression gives no presentation ordering guarantees unless you also specify an ORDER BY clause there.
The same situation is applicable to the OFFSET/FETCH clause. That is, SQL Server lets you define a table expression based on a query with an ORDER BY clause as long as OFFSET/FETCH is also specified, like so:
CREATE VIEW dbo.MyViewAS
SELECT orderid, orderdate, custid, filler
FROM dbo.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
GO
However, you should remember that a query against the table expression gets no presentation ordering guarantees unless you also specify an ORDER BY clause in the outer query. This is fully supported by standard SQL, as the ISO SQL:2008 standard points out:
“A derived table is a table derived directly or indirectly from one or more other tables by the evaluation of an expression, such as a <query expression> or <table expression>. A <query expression> can contain an optional <order by clause>. The ordering of the rows of the table specified by the <query expression> is guaranteed only for the <query expression> that immediately contains the <order by clause>.”
Note that the OFFSET/FETCH filter isn’t supported in indexed views or views with CHECK OPTION.
The ability to specify OFFSET/FETCH in a query that defines a table expression becomes handy when using set operations such as UNION, EXCEPT, and INTERSECT. Set operations allow an ORDER BY clause that applies to the result of the set operation—but not directly in the input queries to the set operation, because by definition it’s a set operation. Because the OFFSET/FETCH filter is tied to the ORDER BY clause, this means that you can’t specify this filter directly in the input queries. However, you can use table expressions as a workaround, like so:
WITH Set1 AS(
SELECT orderid, orderdate, custid, filler
FROM dbo.Orders
WHERE custid = 1
ORDER BY orderdate DESC, orderid DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY
),
Set2 AS
(
SELECT orderid, orderdate, custid, filler
FROM dbo.Orders
WHERE custid = 2
ORDER BY orderdate DESC, orderid DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY
)
SELECT * FROM Set1
UNION ALL
SELECT * FROM Set2;
Logical Query Processing
In terms of evaluation order of query clauses, the OFFSET/FETCH clause is processed right after the ORDER BY clause—or, as I mentioned earlier, you can consider OFFSET/FETCH as actually being part of the ORDER BY specification. Generally, the various query clauses are evaluated in the following order:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
Note that the ORDER BY clause is evaluated last, after SELECT. This means that the expressions in the SELECT list are evaluated before the OFFSET/FETCH filtering and not the other way around. To see why it’s important to understand this fact, consider the following query:
SELECT orderid, orderdate, custid, filler, ROW_NUMBER() OVER(ORDER BY orderdate DESC, orderid DESC) AS rownum
FROM dbo.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;