Local Variables and Transaction (or All at Once Concept)

Let me start with the example of Order of variable
assignment in SELECT statement:

DECLARE @a int,@b int
SET @a=1
SET @b=0

SELECT @a=@a+1,@b=@a
SELECT @a as a,@b as b

What will be the values of @a and @b?
Well, @a will be equal to 2 and @b will be also 2.

You may ask, so what is special about it?

OK, do you know how to explain the following example,
where we swap values of 2 columns in a table.

CREATE TABLE dbo.TestTable
(
Col1 int,
Col2 int
)
INSERT INTO dbo.TestTable(Col1,Col2)
SELECT 1 as Col1,2 as Col2

UPDATE dbo.TestTable
SET Col1=Col2,
Col2=Col1

SELECT * FROM dbo.TestTable

As you can see, in the UPDATE statement above,
the SQL Server`s "All At Once" concept is working,
meaning the value of Col2 is not assigned to Col1
and the value of Col2 is not assigned to Col1 right at the
beginning, but assigned only in the end ,after committing
the results.

Explanation:
The issue is that local variables(and tables) are not effected 
by transaction(s) !
This means that the evaluation/calculation of the values is done
immediately and from left to right, as you saw in the first example.
And here one more example:

DECLARE @str varchar(10)
SET @Str='Test'

BEGIN TRAN
SET @Str='Changed'
PRINT @str
ROLLBACK TRAN
PRINT @str
But this is not the case with an update statement that swaps
columns values (an single UPDATE statement considered
as implicit transaction)

In this case, the results are not committed until the transaction
is committed, in other words SQL Server does not change
the values in memory. Therefore it is not matter what is the
order of the update, I mean, there is no difference between:

UPDATE dbo.TestTable

SET Col1=Col2,

Col2=Col1

and 

UPDATE dbo.TestTable

SET Col2=Col1,

Col1=Col2

Again, this is because an UPDATE is implicit transaction.

Here another example of SQL Server`s All-At-Once approach:
Suppose you have a table with 50 million rows and you
want to update some datetime column in all rows.
Probably you will write something like this:

UPDATE  SomeTable
SET          dtcolumn=GETDATE()

OK, as the result of this update all rows will have the same
datetime,in other words, you will not see a row with different
datetime, all updated rows will have same precision, same ms/seconds.
Am I right?

Now, did you ever thought why it is this way? The Update
statement can take some time, couple of ms or second or minutes,right.
And the answer is: because it is (implicit) transaction.

So, pay extra attention when you decide to use local variables
or local variable tables, they are affected by transactions.

Tweet