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

This article is inspired from two sources. Let us learn today about how to swap variables by updating everything at once concepts.

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 has 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 the effect of transaction on local variant. As seen there local variables are independent of 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:

Solarwinds
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

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

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 result set of the above query returns swapped results.

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

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, for 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 the transaction are sustained across the 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

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

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 the 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 the 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 (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Introduction to CLR – Simple Example of CLR Stored Procedure
Next Post
SQL SERVER – Fix : Error : Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel

Related Posts

16 Comments. Leave new

  • 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

    Reply
  • always ur articles r usefule .. merci ;)

    Reply
  • 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

    Reply
  • DECLARE @A INT, @B INT
    SELECT @A=1,@B=2
    SELECT @A A,@B B
    SELECT @A=@A+@B,@B=@A-@B,@A=@A-@B
    SELECT @A A,@B B

    Reply
  • 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.

    Reply
  • How can i retrieve data in the form of tables in the database from any mdf ,ldf ,ndf file & excel sheet ?

    Reply
  • 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

    Reply
  • Dan Pretselman
    April 1, 2011 7:07 pm

    Great examples, as always.

    For more examples and explanations
    you can refer to:

    .

    Reply
  • Good one

    Reply
  • Amit Kumar Jain
    October 31, 2011 11:16 am

    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.

    Reply
  • THIS IS VERY GOOD ………

    Reply
  • Here’s my solution to swap two variables without declaring a third variable:

    select @VarA = B, @VarB = A
    from (select @VarA A, @VarB B) x

    Reply

Leave a Reply

Menu