February 1, 2013 at 3:15 am
I managed to clobber my own post there I think :ermm:
February 1, 2013 at 3:34 am
call.copse (2/1/2013)
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.
You got it!
When Jeff first published an article on that, many people (including me) reacted in the forums saying that this was not documented, not supported, and not guaranteed to return the correct results. He responded that as long as a certain set of prerequisites -I believe there were five or six in that list- was followed, results were always correct. This was then soon disproven by various people posting repro code where his method failed.
His reponse was an update to the article. Not saying that this method is dangerous and should not be used (well, I do believe that he included a disclaimer somewhere, but he still kept advocating the method), but by extending the list of prerequisites to disallow all the scenarios that were posted before. Unfortuntely, SQL Server version and build number and exact hardware configuration were still not included, even though they should be. At that point, I (and I think most others) more or less gave up - I could put in the effort to find another repro to disprove his claims, but I realized that he would then just extend the list of prerequisites.
I never saw the need for quirky update. I published a method that is almost as fast in most circumstances. I think it's somewhere in one of the enormous topics linked to Jeff's articles on this subject, but it's also published in the MVP Deep Dives book (the first one, not Volume 2).
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.
February 1, 2013 at 5:36 am
Thanks for all your explaination guyz..
--
Dineshbabu
Desire to learn new things..
February 1, 2013 at 5:36 am
Great Friday question, Thanks. I learned something today and am enjoying all the great comments.
February 1, 2013 at 5:49 am
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 🙂
February 1, 2013 at 7:00 am
Good question, as this seems to be very confusing for people with a programming background that start working with SQL.
February 1, 2013 at 7:24 am
Good Question... thank you
February 1, 2013 at 9:44 am
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!
February 1, 2013 at 4:28 pm
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.
February 1, 2013 at 6:12 pm
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.
February 2, 2013 at 3:21 am
Nice one 🙂
February 2, 2013 at 4:40 pm
Great Question..
Thanks..
February 3, 2013 at 6:30 am
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%). 😎
Tom
February 4, 2013 at 4:45 am
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."
February 4, 2013 at 11:45 am
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[/url]
Learn Extended Events
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply