Sql server® 2012 t-sql fundamentals

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;

www.it-ebooks.info

---

60  

Microsoft SQL Server 2012 T-SQL Fundamentals

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

dard 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 expres-

sion 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 expres-

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

www.it-ebooks.info

---