Oops! SQL column swap or SQL column clobber?

Question

Post reply

Oops! SQL column swap or SQL column clobber?

Thomas.Kovarik

SSC Enthusiast

Points: 174
More actions
January 31, 2013 at 8:22 pm

#288577

Comments posted to this topic are about the item Oops! SQL column swap or SQL column clobber?
February 1, 2013 at 2:22 am

This was removed by the editor as SPAM

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

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

Yogeshwar Phull Default port Points: 1434 More actions · Answer 1

Yogeshwar Phull

Default port

Points: 1434

January 31, 2013 at 8:23 pm

#1583254

Thanks for the helpful information...

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

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

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 3

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

manik_anu SSCrazy Points: 2377 More actions · Answer 4

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.

Koen Verbeeck SSC Guru Points: 259075 More actions · Answer 5

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

Kingston Dhasian SSCoach Points: 19798 More actions · Answer 6

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/

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 7

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

Gobikannan SSCrazy Points: 2735 More actions · Answer 8

Gobikannan

SSCrazy

Points: 2735

February 1, 2013 at 12:41 am

#1583287

Good Question.

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 9

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.

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 10

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

demonfox SSCertifiable Points: 6289 More actions · Answer 11

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:

Hugo Kornelis SSC Guru Points: 64770 More actions · Answer 12

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/

call.copse SSCoach Points: 17753 More actions · Answer 13

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: