Single-Table Queries in Microsoft SQL Server 2012

All-at-Once Operations

SQL supports a concept called all-at-once operations, which means that all expressions that appear in the same logical query processing phase are evaluated logically at the same point in time.

This concept explains why, for example, you cannot refer to column aliases assigned in the SELECT clause within the same SELECT clause, even if it seems intuitively that you should be able to. Consider the following query.

SELECT
  orderid,
  YEAR(orderdate) AS orderyear,
  orderyear + 1 AS nextyear
FROM Sales.Orders;

The reference to the column alias orderyear in the third expression in the SELECT list is invalid, even though the referencing expression appears “after” the one in which the alias is assigned. The reason is that logically there is no order of evaluation of the expressions in the SELECT list—the list is a set of expressions. At the logical level, all expressions in the SELECT list are evaluated at the same point in time. Therefore, this query generates the following error.

Msg 207, Level 16, State 1, Line 4
Invalid column name 'orderyear'.

Here’s another example of the relevance of all-at-once operations: Suppose you have a table called T1 with two integer columns called col1 and col2, and you want to return all rows for which col2/col1 is greater than 2. Because there may be rows in the table for which col1 is equal to zero, you need to ensure that the division doesn’t take place in those cases—otherwise, the query fails because of a divide-by-zero error. So you write a query using the following format.

SELECT col1, col2
FROM dbo.T1
WHERE col1 <> 0 AND col2/col1 > 2;

You might very well assume that SQL Server evaluates the expressions from left to right, and that if the expression col1 <> 0 evaluates to FALSE, SQL Server will short-circuit; that is, it doesn’t bother to evaluate the expression 10/col1 > 2 because at this point it is known that the whole expression is FALSE. So you might think that this query never produces a divide-by-zero error.

SQL Server does support short circuits, but because of the all-at-once operations concept in standard SQL, SQL Server is free to process the expressions in the WHERE clause in any order. SQL Server usually makes decisions like this based on cost estimations, meaning that typically the expression that is cheaper to evaluate is evaluated first. You can see that if SQL Server decides to process the expression 10/col1 > 2 first, this query might fail because of a divide-by-zero error.

You have several ways to avoid a failure here. For example, the order in which the WHEN clauses of a CASE expression are evaluated is guaranteed. So you could revise the query as follows.

SELECT col1, col2
FROM dbo.T1
WHERE
  CASE
    WHEN col1 = 0 THEN 'no' -- or 'yes' if row should be returned
    WHEN col2/col1 > 2 THEN 'yes'
    ELSE 'no'
  END = 'yes';

In rows where col1 is equal to zero, the first WHEN clause evaluates to TRUE and the CASE expression returns the string ‘no’ (replace ‘no’ with ‘yes’ if you want to return the row when col1 is equal to zero). Only if the first CASE expression does not evaluate to TRUE—meaning that col1 is not 0—does the second WHEN clause check whether the expression col2/col1 > 2 evaluates to TRUE. If it does, the CASE expression returns the string ‘yes.’ In all other cases, the CASE expression returns the string ‘no.’ The predicate in the WHERE clause returns TRUE only when the result of the CASE expression is equal to the string ‘yes’. This means that there will never be an attempt here to divide by zero.

This workaround turned out to be quite convoluted. In this particular case, you can use a mathematical workaround that avoids division altogether.

SELECT col1, col2
FROM dbo.T1
WHERE (col1 > 0 AND col2 > 2*col1) OR (col1 < 0 AND col2 < 2*col1);

I included this example to explain the unique and important concept of all-at-once operations and to elaborate on the fact that SQL Server guarantees the processing order of the WHEN clauses in a CASE expression.