SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Oops! SQL column swap or SQL column clobber?


Oops! SQL column swap or SQL column clobber?

Author
Message
sestell1
sestell1
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2801 Visits: 3472
Good question, as this seems to be very confusing for people with a programming background that start working with SQL.
mtassin
mtassin
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5382 Visits: 72521
Good Question... thank you



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Miles Neale
Miles Neale
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3458 Visits: 1694
Good question. Got it but had to replay the logic in the head a few times. Warmed me up for Friday morning. Thanks...

Not all gray hairs are Dinosaurs!
jagpatel9
jagpatel9
Say Hey Kid
Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)

Group: General Forum Members
Points: 676 Visits: 69
Good question. In real world, I always use temp table to swap the columns. May be just not want to take risk of data mess.
john.arnott
john.arnott
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2402 Visits: 3059
Gazareth (2/1/2013)
Raghavendra Mudugal (2/1/2013)
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.

I've never seen this definition of Clobbering before - learn something new every day!
Luckily I know the behaviour of SQL in this scenario & got it right :-)


Funny thing, this English language of ours... I didn't realize till today that "clobber" was considered slang. Here we have it in its American usage. The basic meaning is to hit something very hard, or hit person so hard they're knocked out, as in "Muhammed Ali clobbered Joe Frazier." Thus the metaphorical meaning as used in this QOD of destroying the target datum.
udayroy15
udayroy15
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 100
Nice one Smile
pchirags
pchirags
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 616
Great Question..
Thanks..
Tom Thomson
Tom Thomson
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17788 Visits: 12338
Hugo Kornelis (2/1/2013)
That all being said - one other poster (I think it was Tom, but not sure anymore) found a smart way to extend Jeff's method to raise an error if the code would generate incorrect results. Again, buried somewhere in these forums. This is, of course, still firmly in undocumented and unguaranteed territory - but I looked very long and very hard at his trick, and I dare to say with 99.9999% certainty that his claim is reliable - code that employs Tom's trick will either populate correct results, or generate an error.

The original suggestion for error checks was Paul White's, but several people (including Paul himself, as well as both you and me) felt that it still left room for incorrect results without throwing an error. I suggested an improved check which would make the reliability of throwing an error when evaluation order was not as required to deliver correct results very close to 100%. To use a different order but not throw an error the data engine would have to do something quite bizarre, in fact I believe it would have to combine attributes from two different rows when doing a projection, which is of course forbidden by the semantics of projection. While of course T-SQL doesn't conform to the relational model's requirements for its relational calculus, I don't believe that it diverges so far from those requirements as to change the semantics of projections, so I believe the chance of getting incorrect results instead of an error is pretty much the same as the chance of finding an as yet unknown bug that makes some feature that is long established and formally documented in the language definition deliver incorrect results when confronted some unusual data, which might be as high as one in a million (as suggested by your 99.9999%). Cool

Tom

(Bob Brown)
(Bob Brown)
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 1145
The basic meaning is to hit something very hard, or hit person so hard they're knocked out, as in "Muhammed Ali clobbered Joe Frazier." Thus the metaphorical meaning as used in this QOD of destroying the target datum.


I think you got it wrong, as in "Joe Frazier clobbered Muhammed Ali."
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41359 Visits: 18565
Koen Verbeeck (2/1/2013)
Easy one (thanks Itzik! :-)) to end the week. Thanks!


+1



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search