peter-757102 (11/10/2009)
One word Jeff....CLASSThank you for the very clear article and all the testing done to make sure that what you claim is 100% correct. But I wouldn't be me if I had not at least some comments, so here they come:
1) In Figure 28 I personally found the switch from the 3 part assignment problem, solved by using 2 part assignment, straight to a "this is the correct way" which shows 3 parts again...confusing. In fact I think I still don't get the reason why one works and the other doesn't (and I would go 2 parts all the way because of this).
2) At the end when talking about a trigger method to keep a running total correct at all times, you state that it might not work well with batch updates. It is hard to argue with a 'may not work well' opinion, but I just want to state that I can think of a way that could in fact work well.
What about making some sort of temporary Delta/Offset table first for those records that changed and then use this as input in a batch update that corrects all depending records with the appropriate offset without using a pseudo cursor.
Its just an idea, I never tested it and cannot guarantee it runs into unforeseen problems either.
Thanks, Peter. I appreciate the awesome compliment and your observations.
Referring to your item 1, I believe that part of the reason why the code in the "OMG! I Broke It!" section is a bit confusing is because (going out on a limb here), 99.999% of the people wouldn't add 2 to 1 to get to 3. When I first saw a similar bit of code from the two guys I mentioned in the article, they only used the number 1... talk about confusing. Like a lot of folks might, I initially concentrated on the "Who would do this?" rather than the "Oh... I don't know why it does that, but it does." 😛
What the intent of that code is is to show that the 3 part update can create a problem (not always) if the expression (right most part of the 3 part update) relies on the same variable that has already been updated by another expression. The other intent is to show that any problems with the 3 part update can be easily overcome by splitting the 3 part update into two 2 part updates. I see in another post above where at least one person has decided to keep life simple and use only 2 part updates all the time.
For your item number 2, you are correct that you could easily copy the changed rows to a Temp table and do a batch update on those... however, just offseting the rows (I assume you're talking about using an incremental column somewhere in that) won't keep you from needing a "Quirky Update", a real cursor, or a Triangular Join to update the new rows with a running balance. The point is well taken, though. The article is basically incomplete unless the method of using a trigger to update on input is included. I need to resubmit the article to spell Paul Randal's name correctly and to put the correct code in Figure 21... I'll add a section for the trigger update.
Again, thank you for the very thoughtful observations. They're much appreciated.
--Jeff Moden
Change is inevitable... Change for the better is not.