T-SQL Querying (2015)

---

Because this step doesn’t return a table (it returns a cursor), a query with an ORDER BY clause that serves only a presentation ordering purpose cannot be used to define a table expression—that is, a view, an inline table-valued function, a derived table, or a CTE. Rather, the result must be consumed by a target that can handle the records one at a time, in order. Such a target can be a client application, or T-SQL code using a CURSOR object. An attempt to consume a cursor result by a target that expects a relational input will fail. For example, the following attempt to define a derived table is invalid and produces an error:

SELECT orderid, custid
FROM ( SELECT orderid, custid
FROM dbo.Orders
ORDER BY orderid DESC ) AS D;

Similarly, the following attempt to define a view is invalid:

CREATE VIEW dbo.MyOrders
AS

SELECT orderid, custid
FROM dbo.Orders
ORDER BY orderid DESC;
GO

There is an exceptional case in which T-SQL allows defining a table expression based on a query with an ORDER BY clause—when the query also specifies the TOP or OFFSET-FETCH filter. I’ll explain this exceptional case shortly after describing these filters.

The ORDER BY clause considers NULLs as equal. That is, NULLs are sorted together. Standard SQL leaves the question of whether NULLs are sorted lower or higher than known values up to implementations, which must be consistent. T-SQL sorts NULLs as lower than known values (first).

Apply this step to the sample query from Listing 1-2:

ORDER BY numorders

You get the cursor VC6 shown in Table 1-9.

TABLE 1-9 Cursor VC6 returned from step 6

Step 7: Apply the TOP or OFFSET-FETCH filter

TOP and OFFSET-FETCH are query filters that are based on a count of rows and order. This is in contrast to the more classic query filters (ON, WHERE, and HAVING), which are based on a predicate. TOP is a T-SQL specific filter, whereas OFFSET-FETCH is standard. As mentioned, OFFSET-FETCH was introduced in SQL Server 2012. This section describes these filters in the context of logical query processing.

The TOP filter involves two elements in its specification. One (required) is the number or percentage of rows (rounded up) to be filtered. The other (optional) is the order defining which rows to filter. Unfortunately, the ordering specification for TOP (and the same goes for OFFSET-FETCH) is defined by the classic presentation ORDER BY clause in the query, as opposed to being disconnected from it and independent of it. I’ll explain in a moment why this design choice is problematic and leads to confusion. If order is not specified, you should assume it to be arbitrary, yielding a nondeterministic result. This means that if you run the query multiple times, you’re not guaranteed to get repeatable results even if the underlying data remains unchanged. Virtual table VT7 is generated. If the order is specified, cursor VC7 is generated. As an example, the following query returns the three orders with the highest orderid values:

SELECT TOP (3) orderid, custid
FROM dbo.Orders
ORDER BY orderid DESC;

This query generates the following output:

orderid custid
----------- ------
7 NULL
6 MRPHS
5 KRLOS

Even when order is specified, this fact alone doesn’t guarantee determinism. If the ordering isn’t unique (for example, if you change orderid in the preceding query to custid), the order between rows with the same sort values should be considered arbitrary. There are two ways to make a TOP filter deterministic. One is to specify a unique ORDER BY list (for example, custid, orderid). Another is to use a non-unique ORDER BY list and add the WITH TIES option. This option causes the filter to include also all other rows from the underlying query result that have the same sort values as the last row returned. With this option, the choice of which rows to return becomes deterministic, but the presentation ordering still isn’t in terms of rows with the same sort values.

The OFFSET-FETCH filter is similar to TOP, but it adds the option to indicate how many rows to skip (OFFSET clause) before indicating how many rows to filter (FETCH clause). Both the OFFSET and FETCH clauses are specified after the mandatory ORDER BY clause. As an example, the following query specifies the order based on orderid, descending, and it skips four rows and filters the next two rows:

SELECT orderid, custid
FROM dbo.Orders
ORDER BY orderid DESC
OFFSET 4 ROWS FETCH NEXT 2 ROWS ONLY;

This query returns the following output:

orderid custid
----------- ------
3 KRLOS
2 FRNDO

As of SQL Server 2014, the implementation of OFFSET-FETCH in T-SQL doesn’t yet support the PERCENT and WITH TIES options even though standard SQL does. This step generates cursor VC7.

Step 7 is skipped in our example because TOP and OFFSET-FETCH are not specified.

I mentioned earlier that the ordering for TOP and OFFSET-FETCH is defined by the same ORDER BY clause that traditionally defines presentation ordering, and that this causes confusion. Consider the following query as an example:

SELECT TOP (3) orderid, custid
FROM dbo.Orders
ORDER BY orderid DESC;

Here the ORDER BY clause serves two different purposes. One is the traditional presentation ordering (present the returned rows based on orderid, descending ordering). The other is to define for the TOP filter which three rows to pick (the three rows with the highest orderid values). But things get confusing if you want to define a table expression based on such a query. Recall from the section “Step 6: The ORDER BY phase,” that normally you are not allowed to define a table expression based on a query with an ORDER BY clause because such a query generates a cursor. I demonstrated attempts to define a derived table and a view that failed because the inner query had an ORDER BY clause.

There is an exception to this restriction, but it needs to be well understood to avoid incorrect expectations. The exception says that the inner query is allowed to have an ORDER BY clause to support a TOP or OFFSET-FETCH filter. But what’s important to remember in such a case is that if the outer query doesn’t have a presentation ORDER BY clause, the presentation ordering of the result is not guaranteed. The ordering is relevant only for the immediate query that contains the ORDER BY clause. If you want to guarantee presentation ordering of the result, the outermost query must include an ORDER BY clause. Here’s the exact language in the standard that indicates this fact, from ISO/IEC 9075-2:2011(E), section 4.15.3 Derived Tables:

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>.

Despite what you observe in practice, in the following example presentation ordering of the result is not guaranteed because the outermost query doesn’t have an ORDER BY clause:

SELECT orderid, custid
FROM ( SELECT TOP (3) orderid, custid
FROM dbo.Orders
ORDER BY orderid DESC ) AS D;

Suppose you run the preceding query and get a result that seems to be sorted in accordance with the inner query’s ordering specification. It doesn’t mean that the behavior is guaranteed to be repeatable. It could be a result of optimization aspects, and those are not guaranteed to be repeatable. This is one of the most common traps that SQL practitioners fall into—drawing a conclusion and basing expectations on observed behavior as opposed to correctly understanding the theory.

Along similar lines, a common mistake that people make is to attempt to create a “sorted view.” Remember that if you specify the TOP or OFFSET-FETCH options, you are allowed to include an ORDER BY clause in the inner query. So one way some try to achieve this without actually filtering rows is to specify the TOP (100) PERCENT option, like so:

-- Attempt to create a sorted view
IF OBJECT_ID(N'dbo.MyOrders', N'V') IS NOT NULL DROP VIEW dbo.MyOrders;
GO

-- Note: This does not create a "sorted view"!
CREATE VIEW dbo.MyOrders
AS

SELECT TOP (100) PERCENT orderid, custid
FROM dbo.Orders
ORDER BY orderid DESC;
GO

Consider the following query against the view:

SELECT orderid, custid FROM dbo.MyOrders;

According to what I just explained, if you query such a view and do not specify an ORDER BY clause in the outer query, you don’t get any presentation ordering assurances. When I ran this query on my system, I got the result in the following order:

orderid custid
----------- ------
1 FRNDO
2 FRNDO
3 KRLOS
4 KRLOS
5 KRLOS
6 MRPHS
7 NULL

As you can see, the result wasn’t returned based on orderid, descending ordering. Here the SQL Server query optimizer was smart enough to realize that because the outer query doesn’t have an ORDER BY clause, and TOP (100) PERCENT doesn’t really filter any rows, it can ignore both the filter and its ordering specification.

Another attempt to create a sorted view is to specify an OFFSET clause with 0 rows and omit the FETCH clause, like so:

-- Attempt to create a sorted view
IF OBJECT_ID(N'dbo.MyOrders', N'V') IS NOT NULL DROP VIEW dbo.MyOrders;
GO

-- Note: This does not create a "sorted view"!
CREATE VIEW dbo.MyOrders
AS

SELECT orderid, custid
FROM dbo.Orders
ORDER BY orderid DESC
OFFSET 0 ROWS;
GO

-- Query view
SELECT orderid, custid FROM dbo.MyOrders;

When I ran this query on my system, I got the result in the following order:

orderid custid
----------- ------
7 NULL
6 MRPHS
5 KRLOS
4 KRLOS
3 KRLOS
2 FRNDO
1 FRNDO

The result seems to be sorted by orderid, descending, but you still need to remember that this is not guaranteed behavior. It’s just that with this case the optimizer doesn’t have logic embedded in it yet to ignore the filter with its ordering specification like in the previous case.

All this confusion can be avoided if the filters are designed with their own ordering specification, independent of, and disconnected from, the presentation ORDER BY clause. An example for a good design that makes this separation is that of window functions like ROW_NUMBER. You specify the order for the ranking calculation, independent of any ordering that you may or may not want to define in the query for presentation purposes. But alas, for TOP and OFFSET-FETCH, that ship has sailed already. So the responsibility is now on us, the users, to understand the existing design well, and to know what to expect from it, and more importantly, what not to expect from it.

When you’re done, run the following code for cleanup:

IF OBJECT_ID(N'dbo.MyOrders', N'V') IS NOT NULL DROP VIEW dbo.MyOrders;

Further aspects of logical query processing

This section covers further aspects of logical query processing, including table operators (JOIN, APPLY, PIVOT, and UNPIVOT), window functions, and additional relational operators (UNION, EXCEPT, and INTERSECT). Note that I could say much more about these language elements besides their logical query-processing aspects, but that’s the focus of this chapter. Also, if a language element described in this section is completely new to you (for example, PIVOT, UNPIVOT, or APPLY), it might be a bit hard to fully comprehend its meaning at this point. Later in the book, I’ll conduct more detailed discussions, including uses, performance aspects, and so on. You can then return to this chapter and read about the logical query-processing aspects of that language element again to better comprehend its meaning.

Table operators

T-SQL supports four table operators in the FROM clause of a query: JOIN, APPLY, PIVOT, and UNPIVOT.