Oops! SQL column swap or SQL column clobber?

  • Comments posted to this topic are about the item Oops! SQL column swap or SQL column clobber?

  • Thanks for the helpful information...

  • Good one Thomas! I was aware that column values will be swapped 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Actually I'm unable to understand the comments and answers properly. Problem is with the word "CLOBBER".

    But I'm aware of UPDATE behaviour and I know there won't be any issues in the query.

    --
    Dineshbabu
    Desire to learn new things..

  • hi dinesh,

    In that update query, while execute set c1 = c2 the c1 column values will be changed, then in that next statement c2 = c1 how it ill work? so c1 column is called as "clobber". i thing so.... if it is wrong please any one clear out my doubt.......

    **************

    Manik

    Go fast as possible

    Manik
    You cannot get to the top by sitting on your bottom.

  • Easy one (thanks Itzik! :-)) to end the week. Thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Great Question. But the link provided has no information specific to this behavior.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Kingston Dhasian (2/1/2013)


    Great Question. But the link provided has no information specific to this behavior.

    +1

    --
    Dineshbabu
    Desire to learn new things..

  • Good Question.

    -----------------
    Gobikannan

  • Dineshbabu (1/31/2013)


    Actually I'm unable to understand the comments and answers properly. Problem is with the word "CLOBBER".....

    Same here but after checking this http://en.wikipedia.org/wiki/Clobbering, it kind of eased the question to understand.

    Like it says c1 = c2 and c2 = c1, if we do the same in programming language like this

    Dim a As Integer

    Dim b As Integer

    a = 1

    b = 2

    a = b

    b = a

    in the 5th line a = 2 and 6th line b is not 1 as a is already 2 so here the original value of a which was 1 is no longer in picture, so the b is written to b itself with its original value.

    (i guess, i got this write... 😉 )

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Lets drive this a little further:

    CREATE TABLE dbo.Test(a INT NOT NULL, B INT NOT NULL)

    INSERT dbo.Test (a,b) VALUES(1,2)

    INSERT dbo.Test (a,b) VALUES(3,4)

    GO

    DECLARE @a INT = 100

    ,@b INT = 100;

    UPDATE dbo.Test

    SET a = @b-2

    , b = @a

    ,@a = a

    ,@b = b;

    GO

    SELECT * FROM dbo.Test

    GO

    DROP TABLE dbo.Test

    GO

    This also succeeds. And I find this one really nasty with lots of aspects as to why this actually does work.

    Best Regards,

    Chris Büttner

  • Nice question ..

    Dineshbabu (1/31/2013)


    Actually I'm unable to understand the comments and answers properly. Problem is with the word "CLOBBER".

    But I'm aware of UPDATE behaviour and I know there won't be any issues in the query.

    Wikipedia explains it as : "clobbering a file or computer memory is overwriting its contents."

    Explanations states that

    will create a temporary copy of the entire column c1 for later use replacing selective rows of column c2, after column c1 is ‘clobbered’

    since the update happens using the temporary copy , so swapping happens perfectly.

    Nicely explained by raghvendra..

    Clobbering , In other words could mean, Swap the drink by making a cocktail....

    <<>>

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • This was removed by the editor as SPAM

  • Christian Buettner-167247 (2/1/2013)


    Lets drive this a little further:

    CREATE TABLE dbo.Test(a INT NOT NULL, B INT NOT NULL)

    INSERT dbo.Test (a,b) VALUES(1,2)

    INSERT dbo.Test (a,b) VALUES(3,4)

    GO

    DECLARE @a INT = 100

    ,@b INT = 100;

    UPDATE dbo.Test

    SET a = @b-2

    , b = @a

    ,@a = a

    ,@b = b;

    GO

    SELECT * FROM dbo.Test

    GO

    DROP TABLE dbo.Test

    GO

    This also succeeds. And I find this one really nasty with lots of aspects as to why this actually does work.

    I understand that you find it nasty. That's because it IS nasty.

    Any variable assignment in an UPDATE statement is nasty, and I will celebrate the day this "feature" is removed from the product.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (2/1/2013)


    This also succeeds. And I find this one really nasty with lots of aspects as to why this actually does work.

    I understand that you find it nasty. That's because it IS nasty.

    Any variable assignment in an UPDATE statement is nasty, and I will celebrate the day this "feature" is removed from the product.[/quote]

    You're not a fan of the old quirky update then? Much as I dislike the obtuseness of it, I like the performance and admire the ingenuity of it. It does seem to provide results orders of magnitude higher than other methods in specific circumstances - running totals say.

    http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/

    I had not quite registered the use of 'clobber' for such a circumstance (generally it seems to be an unexpected overwriting that breaks desired behaviour). It's a great word and I shall be looking for chances to wheel it out though :hehe:

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply