SQL SERVER – Transaction and Local Variables – Swap Variables – Update All At Once Concept

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)

About these ads

16 thoughts on “SQL SERVER – Transaction and Local Variables – Swap Variables – Update All At Once Concept

  1. This behaviour will be what I expected, thank god
    being ATOMIC

    For example
    UPDATE table
    SET column = GETDATE()

    All columns obtain the same GETDATE() value, instead of milliseconds apart for a large table

  2. For valriable swappings, you dont need a temp variable

    DECLARE @VarA INT
    DECLARE @VarB INT
    DECLARE @VarTemp INT

    SET @VarA = 1
    SET @VarB = 2

    SELECT @VarA =@VarA +@VarB,@VarB=@VarA- @VarB,@VarA=@VarA-@VarB

    SELECT @VarA VarA, @VarB VarB

  3. Pingback: SQL SERVER – Order of Columns in Update Statement Does not Matter Journey to SQL Authority with Pinal Dave

  4. if i write union keyword after select ‘May’,’Fri’ this query then why it is not printing the previous values that are not swapped as when i did this it was showing the final values means swapped values.I couldn’t understand this.

  5. Hello Sir,

    Only today i came across your blog when searching for some sql clarifications. I read your blog. Its amazing. To be frank i was searching for such kind of blog for a long time.

    can you please give me solution for two of my Probs that i am facing for a long time?

    1. What should i install in client place for running my application developed with SQL Server. Right Now am installing MSDE, but is there any other solution for that coz its nearly 35 mb and my online clients feel its bit tedious.

    2. What version of MSDE should I install for Windows 7 and Vista?

    Thank you. Expecting for your reply.
    With regards
    Srinivasan. R

  6. it is a great query, how can we do it if we swap the values in the same column. Like we are storing “Sex” it may be “F” or “M” we have mistakenly store “M” in place of “F” and “F” in place of “M”.

    Now I want to correct the “Sex”, I want to replace “M” to “F” and “F” to “M” on single column.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s