I remember when I read about this concept in a
Itzik Ben-Gan in 2006, I was so excited and could not sleep until daylight. When I encountered the
question about this concept in MSDN Forum, I answered it with the same passion that I read about this mysterious concept. So I made a decision to write an article about it. I want to ask you to be patient and do not see the link of the question until end
up reading this article. Please wait, even you know this concept completely, because I hope this will be an amazing trip.
I explain this with an example using the following code:
-- create a test table
( FirstName NVARCHAR(128), LastName NVARCHAR(128));
-- populate with sample data
( FirstName, LastName )
' Saeid '
LTRIM( RTRIM( FirstName ) ) +
[Corrected FirstName] + LastName
As illustrated with this figure, after executing we encounter this error message:
This error message means that we cannot use an alias in next column expression in the SELECT clause. In the query we create a corrected first name and we want to use it in next column to produce the full name, but the All-at-Once
operations concept tells us you cannot do this because all expressions in the same logical query process phase (here is SELECT) are evaluated logically at the same time.
Because T-SQL is a query language over Relational Database System (Microsoft SQL SERVER), it deals with Sets instead of variables. Therefore, query must be operated on a Set of elements. Now I want to show another example to illustrate
-- drop test table
( ParentId )
dbo.Test ( Id )
-- insert query
( Id, ParentId )
( 1, 2 ),
-- there is not any id = 2 in table
( 2, 2 ) ;
-- update query
Id = 7,
ParentId = 7
-- there is not any id = 7 in table
Id = 1 ;
After execution of this code, as it is shown in the following figure, we see that there is no id=2 in the table, but we can insert it as a foreign key in the table. This is because of All-at-Once operations.
As illustrated in next figure this behavior is repeated in UPDATE query. If we do not have All-at-Once operations feature we should first insert or update the primary key of the table, then modify the foreign key.
Many programmers who are experts in non SQL language, like C# and VB, are confused with this behavior at first, because they fall into the habit that processing a variable in first line of code and using the processed variable
in the next line. They expected to do something like that in the T-SQL. But as I noted earlier, T-SQL is a query language over Relational Database System (Microsoft SQL SERVER), and it deals with Sets instead of variables. Therefore, the query must be operated
on a Set of elements at the same time. Moreover, in each logical query process phase, all expressions processed logically at the same point of time.
This concept impacts on every situation in T-SQL querying. Some days it makes things hard to do and sometimes it makes a fantastic process that we do not expect. To illustrate these impacts I explain four real situations with their
One of the problems that lack of attention to All-at-Once operations concept might produce is writing a code that
might encounter the unexpected error.
We know that square root of a negative number is undefined. So in the code below we put two conditions inside where clause; first condition checks that Id1 is greater than zero. This query
might encounter an error, because the All-at-Once operations concept tells us that these two conditions are evaluated logically at the same point of time. If the first expression evaluates to FALSE, SQL Server will Short Circuit and whole WHERE
clause condition evaluates to FALSE. Therefore, SQL Server can evaluate conditions in WHERE clause in arbitrary order, based on the estimated execution plan.
dbo.Test ( Id1
( Id1, Id2 )
( 0, 0 ), ( 1, 1 ), ( -1, -1 )
id1 > 0
SQRT(Id1) = 1
If after executing the above code you do not receive any error, we need to perform some changes on our code to force SQL Server to choose another order when evaluating conditions in the WHERE clause.
-- create a function that returns zero
@Result = (
Id1 < 1 );
id1 > dbo.fnZero()
One way to avoid encountering error in this query is using CASE like this query:
Id1 < dbo.fnZero()
SQRT(Id1) = 1
( 0, 0 ), ( 1, 1 ), ( 2, 1 )
id2 <> (
Id1 < 1 ) /* this subquery
(Id1) / Id2 = 3 ;
Therefore, the lack of attention to All-at-Once operations concept in T-SQL
might result in encountering the unexpected errors!
Moreover, this concept leads to complexity in debugging T-SQL code. Suppose we have a table “Person”. This table has two columns “FirstName” and “LastName”. For some reasons the values within these columns are mixed with extra
characters. The problem is to write a query that retrieve a new column as Full Name. This code produces our test data:
FirstName NVARCHAR(128) ,
-- populate table with sample data
' Saeid 123 '
' Hasani '
As illustrated in this figure the problem with column “FirstName” is that it’s mixed with extra numbers that should be removed. And the problem with column “LastName” is that it’s mixed with extra space characters before and after
the real Last Name. Here is the code to do this:
( LTRIM( RTRIM( FirstName ) ) , CHARINDEX( N
, LTRIM( RTRIM( FirstName ) ) ) - 1 ) + N
+ LTRIM( RTRIM( LastName ) )
Because of All-at-Once operations we cannot use an alias in next column expression in the SELECT clause. So the code can be very complex to debug.
I found that one way to avoid this problem is using right Code Style and extra comments. The next code is a well formed code style of the former code with same output result and it's easy to debug.
( [FirstName Trim], [
FirstName Trim] - 1 ) +
+ [Corrected LastName]
[FirstName Trim] ::: LTRIM( RTRIM( FirstName ) )
FirstName Trim] ::: CHARINDEX( N
, [FirstName Trim] )
[Corrected LastName] ::: LTRIM( RTRIM( LastName ) )
( LTRIM( RTRIM( FirstName ) )
, CHARINDEX( N
, LTRIM( RTRIM( FirstName ) ) ) - 1
--[Index of first space character in FirstName Trim]
+ LTRIM( RTRIM( LastName )
This concept explains why we cannot use Window Functions in WHERE clause. We use
ad absurdum argument like those we use in mathematics. Suppose that we can use Window Functions in WHERE clause. Please see the following code.
dbo.Test ( Id
( 1001 ), ( 1002 ) ;
Id = 1002
Id) = 1;
All-at-Once operations tell us these two conditions evaluated logically at the same point of time. Therefore, SQL Server can evaluate conditions in WHERE clause in arbitrary order, based on estimated execution plan. So the main question here is which condition
We can think about these two orders:
SQL Server check if ( Id = 1002 ) first,
Then check if ( ROW_NUMBER() OVER(ORDER BY Id) = 1 )
In this order the output will be ( 1002 ).
Then check if ( Id = 1002 )
In this order the output will be empty.
So we have a paradox.
This example shows why we cannot use Window Functions
in WHERE clause. You can think more about this and find why Window Functions are allowed to be used just in SELECT and ORDER BY clauses!
This is the most exciting part of this article that I love it. The question is that how to swap values of two columns in a table without using a temporary table? This code provide sample data for us:
Consider that in all other non SQL languages, we have to use a temporary variable to swap values between two variables. If we want to see the problem from the non SQL programmer, we should do something like this prototype:
If we see the problem from a SQL programmer we can translate the above prototype by using a temporary table “#swap”. The code should be like this:
FirstName = a.LastName ,
LastName = a.FirstName
a.PersonId = b.PersonId
This code works fine. But the main question is that how much time above script needs to run, if we have millions of records?
If we are known with All-at-Once operations concept in T-SQL, we can do this job through one update statement with the following simple code:
FirstName = LastName ,
LastName = FirstName ;
In definition section I noted that the query must be operated on a Set of elements. What will happen if a query deal with multiple tables? In such queries we use table operators like JOIN and APPLY inside FROM clause. By the way,
these operators are logically evaluated from left to right. Because we have multiple Sets, first we need to transform them to a Set then we have All-at-Once operations concept. Therefore, this concept is not applicable to the table operators in FROM clause.
All-at-Once operations is one of the most important concept in T-SQL language that has an extreme impact on our T-SQL programming, code style and performance tuning solutions.