Please can somebody explain how quirky updates work?

No, I cant. But Jeff Moden can here: http://sqlserverchallenges.com/TSQL_Challenges/Challenge_13/solutions/jeff_moden or here: http://www.sqlservercentral.com/articles/T-SQL/68467/

In essence, I understand it as a section of script that has never produced the wrong results but cant be conclusively proven to always be 100% reliable. IE dont use it in production unless you are running some other benchmark/validation.

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

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!