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 as if 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 is invalid in the third expression in the SELECT list, even though the referencing expression appears “after” the one where the alias is assigned. The reason is that logically there is no order of evaluation of the expressions in the SELECT list—it’s 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 had a table called T1 with two integer columns called col1 and col2, and you wanted to return all rows where col2⁄col1 is greater than 2. Because there may be rows in the table where col1 is equal to 0, you need to ensure that the division doesn’t take place in those cases—otherwise, the query fails because of a divideby– zero error. So if you write a query using the following format:
SELECT col1, col2
FROM dbo.T1
WHERE col1 <> 0 AND col2⁄col1 > 2;
You 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–byzero error. SQL Server does support short circuits, but because of the all–at–once operations concept in ANSI SQL, SQL Server is free to process the expressions in the WHERE clause in any order that it likes. 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 try and 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 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 10⁄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, and in this particular case we can use a simpler mathematical workaround that avoids division altogether:
SELECT col1, col2
FROM dbo.T1
WHERE col1 <> 0 and col2 > 2*col1;
I included this example to explain the unique and important all–at–once operations concept, and the fact that SQL Server guarantees the processing order of the WHEN clauses in a CASE expression.
With the concept of all–at–once in mind, can you figure out how to write an UPDATE statement that swaps the values in the columns col1 and col2? In most programming languages where expressions and assignments are evaluated in some order (typically left to right), you need a temporary variable. However, because in SQL all assignments take place as if at the same point in time, the solution is very simple:
UPDATE dbo.T1
SET col1 = col2, col2 = col1;
In both assignments the source column values used are those prior to the update, so you don’t need a temporary variable.
by Itzik Ben–Gan
