question

Ian Roke avatar image
Ian Roke asked

Please can somebody explain how quirky updates work?

I have been reading through Robyn Page's SQL Server Cursor Workbench and she has an example of a "quirky update" like so...

Create tables used.

CREATE TABLE #cb (cb_ID INT IDENTITY(1,1),--sequence of entries 1..n
    Et VARCHAR(10), --entryType
    amount money)--quantity

INSERT INTO #cb(et,amount) SELECT 'balance',465.00
INSERT INTO #cb(et,amount) SELECT 'sale',56.00
INSERT INTO #cb(et,amount) SELECT 'sale',434.30
INSERT INTO #cb(et,amount) SELECT 'purchase',20.04
INSERT INTO #cb(et,amount) SELECT 'purchase',65.00
INSERT INTO #cb(et,amount) SELECT 'sale',23.22
INSERT INTO #cb(et,amount) SELECT 'sale',45.80
INSERT INTO #cb(et,amount) SELECT 'purchase',34.08
INSERT INTO #cb(et,amount) SELECT 'purchase',78.30
INSERT INTO #cb(et,amount) SELECT 'purchase',56.00
INSERT INTO #cb(et,amount) SELECT 'sale',75.22
INSERT INTO #cb(et,amount) SELECT 'sale',5.80
INSERT INTO #cb(et,amount) SELECT 'purchase',3.08
INSERT INTO #cb(et,amount) SELECT 'sale',3.29
INSERT INTO #cb(et,amount) SELECT 'sale',100.80
INSERT INTO #cb(et,amount) SELECT 'sale',100.22
INSERT INTO #cb(et,amount) SELECT 'sale',23.80

DECLARE @cb TABLE(cb_ID INT,--sequence of entries 1..n
    Et VARCHAR(10), --entryType
    amount money,--quantity
    total money)

DECLARE @total money 

SET @total = 0

Now for the quirky update

INSERT INTO @cb(cb_id,Et,amount,total) 
SELECT cb_id,Et,CASE WHEN Et='purchase' 
    THEN -amount 
    ELSE amount 
    END,0 FROM #cb

UPDATE @cb 
SET @total = total = @total + amount FROM @cb 

SELECT [Entry Type]=Et, [amount]=amount, 
    [balance after transaction]=total FROM @cb ORDER BY cb_id

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
sql-server-2008t-sqlupdate
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It is better because it is faster by several orders of magnitude. Just look at the data, and the graph at the end of the section http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/#fourth And you'll see that the triangular join was so slow we couldn't even perform the test when there was a decent number of rows. It doesn't scale
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

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.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 probably one of the best, mind-blowing articles I have ever read on SSC!
2 Likes 2 ·
Jeff Moden avatar image
Jeff Moden answered

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

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Thanks for the feedback Jeff. Good to see you on these forums! :-)
0 Likes 0 ·
Thanks, Ian. Glad to be here but not sure how much time I'll actually spend here. My first love, of course, is SQLServerCentral.com. ;-) Just curious... did the article on the "Triangular Joins" help in understanding why inequalities in joins have the potential (they're not all bad) for being a server killer?
0 Likes 0 ·
Robyn and I added the timings and a graph to illustrate just how bad the triangular join performed
0 Likes 0 ·
Jeff Moden avatar image
Jeff Moden answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

(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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered
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!
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Jeff Moden Please count my vote for Paul White for President!
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
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!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.