Journey to SQL Authority with Pinal Dave

This article is inspired from two sources.

1) My year old article – SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT
2) Discussion with SQL Server MVP – Jacob Sebastian – SQLAuthority News – Author Visit – SQL Hour at Patni Computer Systems

I usually summarize my article at the end, but this time let me summarize first and we will understand the article next.

Summary : Transaction have no effect over memory variables. When UPDATE statement is applied over any table (physical or memory) all the updates are applied at one time together when the statement is committed.

First of all I suggest that you read the article listed above about Effect of transaction on local variable. As seen there local variables are independent from any transaction effect.

Example 1:
PRINT 'After ROLLBACK example'
DECLARE @FlagINT INT
SET @FlagInt = 1
PRINT @FlagInt ---- @FlagInt Value will be 1
BEGIN TRANSACTION
SET @FlagInt = 2
PRINT @FlagInt ---- @FlagInt Value will be 2
ROLLBACK TRANSACTION
PRINT @FlagInt ---- @FlagInt Value will be ?
GO

Example 2:
PRINT 'After COMMIT example'
DECLARE @FlagINT INT
SET @FlagInt = 1
PRINT @FlagInt ---- @FlagInt Value will be 1
BEGIN TRANSACTION
SET @FlagInt = 2
PRINT @FlagInt ---- @FlagInt Value will be 2
COMMIT TRANSACTION
PRINT @FlagInt ---- @FlagInt Value will be ?
GO

Let us take example of any programming language where we want to swap two variables.

For example we have two variables VarA and VarB and we want to swap values of both of them we will have to do following logic where we use temp variables.

TempVar = VarA;
VarA = VarB;
VarB = TempVar;

In case of SQL Server the logic has to be followed.

Example 3:
DECLARE @VarA INT
DECLARE @VarB INT
DECLARE @VarTemp INT

SET @VarA = 1
SET @VarB = 2

SELECT @VarA VarA, @VarB VarB
SELECT @VarTemp = @VarA, @VarA = @VarB, @VarB = @VarTemp
SELECT @VarA VarA, @VarB VarB
GO

The resultset of the the above query returns swapped results.

Looking at both of the above example it is not clear how they are related. Now let us come to the main point of this post. How to swap values in SQL without using temp variable?

Now we have understood that from example 1 that local variables are independent of the transaction. From example 3 we have understood the importance of the third temp variable while swapping the values. If we situation where transaction are sustained across complete operation we may not need third variable.

If we can have following both the operation happening at the same time, it will save troubles of temp variables.
VarA = VarB; VarA = VarB;

Let us see following example where two columns are swapped. It is interesting to note that there is no temp column or any other logic is used. Both of the columns are swapped by just assigning to each other.

Example 4:

DECLARE @TableA TABLE
(Months VARCHAR(100), Days VARCHAR(100))
INSERT INTO @TableA (Months, Days)
SELECT 'Jan', 'Mon'
UNION
SELECT 'Feb', 'Tue'
UNION
SELECT 'Mar', 'Wed'
UNION
SELECT 'Apr', 'Thu'
UNION
SELECT 'May', 'Fri'

SELECT *
FROM @TableA

UPDATE @TableA
SET Months = Days,
Days = Months

SELECT *
FROM @TableA
GO

Now looking at above query it is clear that UPDATE statement is updating both the columns at the same time and swaps the data without using temp column. This is happening due to transaction. When UPDATE statement is executed it swaps the data internally and stores in the memory, as soon as UPDATE statement is committed it puts the new values (swapped values) in the columns.

I think it is possible due to transactional behavior of the UPDATE statement.

Let me know if you want to add anything more to this subject or have similar examples.

Reference : Pinal Dave (http://blog.SQLAuthority.com)