question

Holy moly! Thanks for the kudos, folks. With all of the "anti-quirky-update" sentiment going on in the disussion that followed that article, I'm truly humbled by and very appreciative of your comments above. Thank you very much.

Ian, you asked "Now why is that a better way of doing things than just taking the original #cb table and doing an inner join like so?"

SELECT [Entry Type] = MIN(#cb.Et), 
    [amount] = MIN(#cb.amount), 
    [balance after transaction] =
        SUM(CASE WHEN total.Et = 'purchase' 
        THEN -total.amount 
        ELSE total.amount END)

FROM #cb total 
INNER JOIN #cb ON total.cb_id <= #cb.cb_id 

GROUP BY #cb.cb_id ORDER BY #cb.cb_id

That method is known as a "Triangular Join" and the following link leads to an article that explains why that's such a bad thing...

Hidden RBAR: Triangular Joins

--Jeff Moden

IE dont use it in production unless you are running some other benchmark/validation.

Just to be clear on my true postion on the quirky update... In the article I wrote on the subject of running totals and the quirky update, I offered up some validation code to not only prove that the quirky update worked for the examples given but to also give people an additional tool to make them less nervous about using it. In real life, I don't do the checks anymore than someone would write a check to see if their SELECT worked correctly because just like a SELECT, once you get one running correctly and safely (especially in a Temp table), they'll run correctly forever.

Yes, I'm well aware of what people say about the potential for a CU or SP mucking up the works. Two things on that... first, the quirky update has survived all CUs, Hot Fixes, and SPs since before SQL Server was SQL Server. Second, no one in their right mind would blindly install a CU, Hot Fix, or SP without doing full regression testing on all the code. That's the only time I do checks on the quirky update unless a customer specifically asks for other checks.

I'll also say that if folks don't take the time to do the proper research on how to use the quirky update and don't do the proper unit testing (as they should with any code), then they patently shouldn't use the quirky update. "Black Arts" code isn't for casual users by any means. Casual users of T-SQL and people in a hurry should use a well written FORWARD ONLY, READ ONLY, STATIC cursor or a CLR, instead.

--Jeff Moden

(I tried to make this a comment to Jeff's answer, but it is too long)

Jeff makes good points, and there are times when a quirky update is appropriate. As he mentioned, it tends to be faster than most other solutions and uses only T-SQL.

But I counsel caution before using it. Remember that even if you are fully comfortable with "Black Arts" coding, in many organizations the person who maintains the code you write is likely to not be you, or at least not always you for the whole time it is in production. The next person probably will not understand it fully, so if you do use it, I suggest commenting it excessively.

Also, remember this is not part of the SQL Standard. This means nothing if you are using SQL Server only, but I try to use standards compliant code unless there is a good reason not to.

This is a perfectly valid technique that will work consistently if you write it correctly, and is very unlikely to be changed in a mere service pack (and fairly unlikely to be changed in any major release in the near future). Still, it is one that is hard for many people to understand and it is not standards compliant, and those are reasons to judge carefully if it is worth using.

Actually, Paul White came up with a method to ensure that it will always inform you if it goes wrong and, in the process, the "test" forces the code to always do the right thing. I still have a fair bit of testing to do on it buy my immediate feeling is... Paul White for President!

Quirky Update was introduced in Sybase because there were no Window Functions then, and there were a whole range of accounting and statistical operations that couldn't be done using the version of SQL that was available at the time. There are actually two areas of functionality. the first is not controversial at all. Variables were allowed in updates. This is not in the SQL Standard. However, it generally works well, though occasionally one can get oddities happening. The second is to use a variable to accumulate a value, or to carry over a value from a previous row. This relies on an assumption about the order in which SQL Server actually does the Update. In the old Sybase where we used the technique, that was fine, but subsequent optimisation and parallelisation has muddied the waters. This is why Robyn called it 'quirky'. The technique is fine, if you are very careful with it, and test the results remorselessly over a lot of different sizes of table. Don't let various MVPs bully you into not using it. The reason we have to use it is that SQL Server still hasn't completely implemented the Window functions required by the SQL Standard. When they've done so, we can stop using it!