InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 22:55:37 GMT20http://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]J Livingston SQL (2/13/2012)[/b][hr][quote][b]gcresse (2/13/2012)[/b][hr]The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.[/quote]ok.. I cant see what you can see......but in your situation then I would be asking myself "why have I got too many columns...should I be thinking about rows instead?"...I assume this is what you mean by "not be wide enough"anyway good luck.kind regards[/quote]I agree... think rows instead of columns here even if you don't use the QU.As a sidebar, any process that has thousands of steps should probably be reevaluated a bit. ;-)As another sidebar, can you move the discussion about such a wide table to a new thread, please? Thanks.Mon, 13 Feb 2012 17:00:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]gcresse (2/13/2012)[/b][hr]The temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.[/quote]ok.. I cant see what you can see......but in your situation then I would be asking myself "why have I got too many columns...should I be thinking about rows instead?"...I assume this is what you mean by "not be wide enough"anyway good luck.kind regardsMon, 13 Feb 2012 12:53:35 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxThe temp table would not be wide enough. I need the ability to calculate running percents on each component in the final product, and often times there are thousands of them.Mon, 13 Feb 2012 12:44:29 GMTgcressehttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]gcresse (2/13/2012)[/b][hr]I just read this article about SQL Server 2012's new features (http://blog.tallan.com/2011/12/08/sql-server-2012-windowing-functions-part-1-of-2-running-and-sliding-aggregates/) and I'm thinking it *might* solve my problem, as long as I can join to other tables. I had to give up on the Quirky Update because I had to try to jam every column I needed into the same row and in some cases the row was too wide for SQL Server to handle.[/quote]whilst you wait for 2012.....could you consider creating a temp table from all of your sub queries / joins etc....and then running QU on the temp table?Mon, 13 Feb 2012 11:05:45 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxI just read this article about SQL Server 2012's new features (http://blog.tallan.com/2011/12/08/sql-server-2012-windowing-functions-part-1-of-2-running-and-sliding-aggregates/) and I'm thinking it *might* solve my problem, as long as I can join to other tables. I had to give up on the Quirky Update because I had to try to jam every column I needed into the same row and in some cases the row was too wide for SQL Server to handle.Mon, 13 Feb 2012 10:50:26 GMTgcressehttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxGreat article, and an excellent technique I've been utilising (with care!) for a while.I do however raise an argument against your statement that "The "Quirky Update" will work just fine with all 2 part SET statements".Try making the Update @OMG statement into this, using only 2 part statements, and see what you get :)[code="sql"] UPDATE @OMG SET @N = @N + 1, @N = @N + 1, SomeInt = @N + 1 FROM @OMG OPTION (MAXDOP 1)[/code]Again we see that it works fine for the first row, but then fails after that. As mentioned elsewhere, it is more a factor of updating the variable twice rather than it being in 2 or 3 part statements.TimTue, 31 Jan 2012 04:16:24 GMTGrumpyDBAhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]PBaekdal (1/29/2012)[/b][hr]Nice and enlightening ... on a par with your original tally table article.Cheers,Pete.[/quote]Thanks Pete. I really appreciate the feedback.Sun, 29 Jan 2012 22:09:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxNice and enlightening ... on a par with your original tally table article.Cheers,Pete.Sun, 29 Jan 2012 19:58:26 GMTPBaekdalhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]charles.southey (1/27/2012)[/b][hr]This is a good example of the hoops you sometimes have to go through to carry out some conceptually quite simple tasks in basic T-SQL. For a much simpler approach to this problem (and many others) you might want to check out our T-SQL function libraries: [url]http://www.totallysql.com/products/sqlrollingstats[/url].[/quote]It's a shame you decided to release this as a commercial product (with ambitious pricing!), rather than as open source on a site like CodePlex. The choice to use SQLCLR UDTs is an interesting one; I have not found these to perform very well in general. It is concerning that you use shared state in a core UNSAFE assembly; it seems unlikely you are able to clean up successfully in all situations e.g. after a 'rude abort'. Nice idea in some ways, pity about the commercialism.Fri, 27 Jan 2012 04:07:50 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxThis is a good example of the hoops you sometimes have to go through to carry out some conceptually quite simple tasks in basic T-SQL. For a much simpler approach to this problem (and many others) you might want to check out our T-SQL function libraries: [url]http://www.totallysql.com/products/sqlrollingstats[/url].Fri, 27 Jan 2012 03:49:39 GMTcharles.southeyhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]gcresse (10/31/2011)[/b][hr]Am I expecting too much?[/quote]I believe so. What you're trying to do is actually breaking Rule 5 which stipulates "No Joins". The Sub-queries you're running are correlated subqueries which are joins.Mon, 31 Oct 2011 19:22:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxIs it possible to include a subquery on the table being updated in the quirky update? I'm experimenting with the following code but the subqueries are not returning updated data that I expect from a pseudo-cursor. The first row updates fine, but when later rows need to get the data from the first row, the subquery comes back with 0 instead of the correct value. Am I expecting too much?[code="sql"]UPDATE ASET @Anchor = VntPodKey, @Pct = A.Pct2008 = CASE WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN -- REDUCING OR GAINING GALLONS DOES NOT CHANGE PERCENTS - GET THE LAST PERCENT FOR THIS LOT (SELECT TOP 1 B.Pct2008 FROM #tmpVintage B WHERE B.VntLotKey = A.VntLotKey AND B.VntTranDate < A.VntTranDate ORDER BY B.VntTranDate DESC) ELSE -- INCREASING GALLONS ON A 2-SIDED TRANSACTION CASE WHEN A.VntPodCount = 2 THEN -- GET THE PCT FROM THE 'FROM' SIDE OF THE TRANSACTION (((SELECT B.Pct2008 FROM #tmpVintage B WHERE B.VntPodKey = A.VntPodKey AND B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2008 + (SELECT TOP 1 C.Run2008 FROM #tmpVintage C WHERE C.VntLotKey = A.VntLotKey AND C.VntTranDate < A.VntTranDate ORDER BY C.VntTranDate DESC)) -- ADD THE LAST RUNNING TOTAL FOR THE LOT TO THE QTY2008 FOR THE RUN2008 QTY / A.VntRunTranQty -- DIVIDE THE RUN2008 BY THE LOT'S RUNNING TOTAL TO GET THE PERCENT END END, @Qty = A.Qty2008 = CASE WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN @Pct * A.VntTranQty / 100 ELSE ((SELECT B.Pct2008 FROM #tmpVintage B WHERE B.VntPodKey = A.VntPodKey AND B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2008 END, @Run = A.Run2008 = CASE WHEN A.VntTranQty < 0 OR (A.VntTranQty > 0 AND A.VntPodCount = 1) THEN @Pct * A.VntRunTranQty / 100 ELSE (((SELECT B.Pct2008 FROM #tmpVintage B WHERE B.VntPodKey = A.VntPodKey AND B.VntIssueRcpt = 'I') * A.VntTranQty) -- Qty2008 + (SELECT TOP 1 C.Run2008 FROM #tmpVintage C WHERE C.VntLotKey = A.VntLotKey AND C.VntTranDate < A.VntTranDate ORDER BY C.VntTranDate DESC)) -- ADD THE LAST RUNNING TOTAL FOR THE LOT TO THE QTY2008 FOR THE RUN2008 QTY END FROM #tmpVintage A WITH (TABLOCKX)WHERE A.VntTagKey = 0OPTION (MAXDOP 1) [/code]Mon, 31 Oct 2011 16:38:23 GMTgcressehttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]SQLkiwi (7/15/2011)[/b][hr...Beware drawing performance conclusions from CTP (beta) software. [/quote]Oh how I wish everyone would underastand that!Paul, you just aquired my vote for God's chief deputy for sensible DB design.Fri, 15 Jul 2011 19:24:30 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxThere's something missing in the code Wayne... and I think it's the reason why it's so comparatively slow. Peter Larsson sent me some code for Running Totals. I'll dig it out when I get home.Fri, 15 Jul 2011 15:34:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]SQLkiwi (7/15/2011)[/b][hr]Hi Wayne,It always confuses me when a CTE and subquery are compared like that - they are equivalent.[/quote]Just trying to confuse you Paul. Hey! - it worked! :-)I know it's the same(pre-defined subquery); I tried it just to ensure.[quote]Beware drawing performance conclusions from CTP (beta) software. From what I have seen so far, it would be quite possible to optimize the running total scenario further. I have no inside information on whether that is planned or not.[/quote]Definitely understand that. And hopefully that is exactly what is going on. What is nice is that this is now available (now = soon...)Fri, 15 Jul 2011 12:39:44 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxHi Wayne,It always confuses me when a CTE and subquery are compared like that - they are equivalent.Beware drawing performance conclusions from CTP (beta) software. From what I have seen so far, it would be quite possible to optimize the running total scenario further. I have no inside information on whether that is planned or not.Fri, 15 Jul 2011 11:55:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]Jeff Moden (7/14/2011)[/b][hr]It's not going to matter... even though I'm seriously disappointed in the performance (as you've measured), the new SUM() is faster than a While Loop, easier to write, does not require a table update, and it's "officially" supported.I normally don't go near the bleeding edge but I guess I'm going to have to load up Denalli CTP3 and do some testing on my own. Thanks for the early feedback, Wayne. I really appreciate the time you put into this one.[/quote]No problem Jeff - I have a pretty strong interest in this, since I'm sitting on the proverbial fence on the use of the QU. (The only thing I don't like about it is the non-MS documented rules on how to make it work.)Plus... since you're rewriting this article again, it would probably be good to have this in there to be complete. (nudge nudge)Fri, 15 Jul 2011 11:49:39 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]SQLkiwi (7/14/2011)[/b][hr]Wayne, you can avoid the join:[/quote]:blush: I should have seen that. (Coding when tired...)Okay, I've tried these three different methods:[code="sql"]-- DENALI CTP3 Running Totals with enhanced over clause (cte).;WITH cte AS(SELECT AccountRunningTotal, AccountRunningCount, ART = SUM(Amount) OVER (PARTITION BY AccountID ORDER BY Date, TransactionDetailID), ARC = SUM(1) OVER (PARTITION BY AccountID ORDER BY Date, TransactionDetailID) FROM dbo.TransactionDetail)UPDATE cte SET AccountRunningTotal = ART, AccountRunningCount = ARCGO[/code][code="sql"]-- DENALI CTP3 Running Totals with enhanced over clause (subquery).;UPDATE target SET AccountRunningTotal = ART, AccountRunningCount = ARC FROM (SELECT AccountRunningTotal, AccountRunningCount, ART = SUM(Amount) OVER (PARTITION BY AccountID ORDER BY Date, TransactionDetailID), ARC = SUM(1) OVER (PARTITION BY AccountID ORDER BY Date, TransactionDetailID) FROM dbo.TransactionDetail ) targetGO[/code][code="sql"]-- DENALI CTP3 Running Totals with enhanced over clause (Joined cte).;WITH cte AS(SELECT TransactionDetailID, [Date], AccountID, Amount, AccountRunningTotal = SUM(Amount) OVER (PARTITION BY AccountID ORDER BY Date, TransactionDetailID), AccountRunningCount = SUM(1) OVER (PARTITION BY AccountID ORDER BY Date, TransactionDetailID), NCID FROM dbo.TransactionDetail)UPDATE td SET AccountRunningTotal = cte.AccountRunningTotal, AccountRunningCount = cte.AccountRunningCount FROM dbo.TransactionDetail td JOIN cte ON cte.TransactionDetailID = td.TransactionDetailIDGO[/code]All are way worse than the QU:[img]http://www.sqlservercentral.com/Forums/Attachment9343.aspx[/img]Fri, 15 Jul 2011 11:40:19 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]SwePeso (7/15/2011)[/b][hr][quote][b]SQLkiwi (7/15/2011)[/b][hr]No, of course not. Neither SUM nor COUNT can use OVER ... ORDER BY before Denali.[/quote]I think Peter meant if existing code was ported to Denali and the functionality was changed because of different defaults.[/quote]Who knows? I think we covered most of the possible interpretations between us though.Fri, 15 Jul 2011 07:46:32 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]SQLkiwi (7/15/2011)[/b][hr]No, of course not. Neither SUM nor COUNT can use OVER ... ORDER BY before Denali.[/quote]I think Peter meant if existing code was ported to Denali and the functionality was changed because of different defaults.Fri, 15 Jul 2011 07:37:55 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]peter-757102 (7/15/2011)[/b][hr]Ineresting amount of control, but am i right to read in this that the default for this new functionality alters the meaning of existing code?[/quote]No, of course not. Neither SUM nor COUNT can use OVER ... ORDER BY before Denali. It would be extraordinary if Microsoft were to change the semantic of existing (roughly standard) SQL in a new release.For full details of the new OVER clause syntax see [url]http://msdn.microsoft.com/en-us/library/ms189461(v=SQL.110).aspx[/url]Fri, 15 Jul 2011 07:34:51 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxOnly if you add the ORDER BY clause, then the default framing will be "between unbounded preceding and current row".If the ORDER BY clause is not there, the default framing is "between unbounded preceding and unbounded following", which means the entire partition key as it is today.Fri, 15 Jul 2011 04:27:27 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]SwePeso (7/15/2011)[/b][hr]There is an additional clause for the order by part, which defaults to "first row" to "current row" in the partition window.These three options are avaiable with the RANGE predicate:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWRANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (equals without order by)Things are different with ROWS predicate:ROWS BETWEEN @x PRECEDING AND @y FOLLOWINGROWS BETWEEN @x PRECEDING AND CURRENT ROWROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW[/quote]Ineresting amount of control, but am i right to read in this that the default for this new functionality alters the meaning of existing code?Fri, 15 Jul 2011 03:55:47 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxThere is an additional clause for the order by part, which defaults to "first row" to "current row" in the partition window.These three options are avaiable with the RANGE predicate:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWRANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (equals without order by)Things are different with ROWS predicate:ROWS BETWEEN @x PRECEDING AND @y FOLLOWINGROWS BETWEEN @x PRECEDING AND CURRENT ROWROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWFri, 15 Jul 2011 03:34:37 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]SQLkiwi (7/14/2011)[/b][hr]Wayne, you can avoid the join:[code="sql"]UPDATE targetSET AccountRunningTotal = art, AccountRunningCount = arcFROM ( SELECT td.AccountRunningTotal, td.AccountRunningCount, art = SUM(td.Amount) OVER (PARTITION BY td.AccountID ORDER BY td.Date, td.TransactionDetailID), arc = COUNT_BIG(*) OVER (PARTITION BY td.AccountID ORDER BY td.Date, td.TransactionDetailID) FROM dbo.TransactionDetail AS td) AS target[/code][img]http://www.sqlservercentral.com/Forums/Attachment9334.aspx[/img][/quote]What exactly limited the running total in that query to all the previous plus current "actvive" row? Is this new behavior of the existing aggregate functions? Either way, I find the notation very non-explicit and in that it propbalbly kills over a ton of existing code if it has changed.Fri, 15 Jul 2011 03:22:58 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxThat should run quite a bit faster.Thu, 14 Jul 2011 13:31:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxWayne, you can avoid the join:[code="sql"]UPDATE targetSET AccountRunningTotal = art, AccountRunningCount = arcFROM ( SELECT td.AccountRunningTotal, td.AccountRunningCount, art = SUM(td.Amount) OVER (PARTITION BY td.AccountID ORDER BY td.Date, td.TransactionDetailID), arc = COUNT_BIG(*) OVER (PARTITION BY td.AccountID ORDER BY td.Date, td.TransactionDetailID) FROM dbo.TransactionDetail AS td) AS target[/code][img]http://www.sqlservercentral.com/Forums/Attachment9334.aspx[/img]Thu, 14 Jul 2011 12:11:04 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxIt's not going to matter... even though I'm seriously disappointed in the performance (as you've measured), the new SUM() is faster than a While Loop, easier to write, does not require a table update, and it's "officially" supported.I normally don't go near the bleeding edge but I guess I'm going to have to load up Denalli CTP3 and do some testing on my own. Thanks for the early feedback, Wayne. I really appreciate the time you put into this one.Thu, 14 Jul 2011 11:37:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxFYI:I just compared this Quirky Update code to the newest enhancements in the next version of SQL Server (code named "Denali") CTP3 and found that the Quirky Update performs far greater than the new enhancements in Denali CTP3 currently do. (The Quirky Update was not modified to perform the safety check.)Denali CTP3 code:[code="sql"]-- DENALI CTP3 Running Totals with enhanced over clause.;WITH cte AS(SELECT TransactionDetailID, [Date], AccountID, Amount, AccountRunningTotal = SUM(Amount) OVER (PARTITION BY AccountID ORDER BY Date, TransactionDetailID), AccountRunningCount = SUM(1) OVER (PARTITION BY AccountID ORDER BY Date, TransactionDetailID), NCID FROM dbo.TransactionDetail)UPDATE td SET AccountRunningTotal = cte.AccountRunningTotal, AccountRunningCount = cte.AccountRunningCount FROM dbo.TransactionDetail td JOIN cte ON cte.TransactionDetailID = td.TransactionDetailIDGO--===== Verify the the running total worked on the -- TransactionDetail table USE TEMPDB --Takes 10 seconds on my machine EXEC dbo.VerifyGO[/code]Results (comparing Quirky Update to new features):(Not sure why the Quirky Update is showing zero writes - possible bug in Profiler with CTP3?)[img]http://www.sqlservercentral.com/Forums/Attachment9333.aspx[/img]Thu, 14 Jul 2011 09:20:57 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]Nadrek (5/12/2011)[/b][hr][quote][b]Jeff Moden (5/11/2011)[/b][hr]Sorry... I missed this... Maybe not in your country or language but that's not what's implied by such language in my country and the two statements have drastically different meanings. I actually take offense to the wording or would prefer that you not use it again and also change existing wording.[/quote]My apologies; I did not mean any offense. I've edited my code and post with the more precise version.That said, while I do not need to use the quirky update, did anyone ever notice what mistake I made on the 2 part version, so I can avoid making it again?[/quote]Thank you for your consideration.I'll also go back and try to figure out your question. Thanks, Nadrek.Thu, 12 May 2011 18:58:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]Jeff Moden (5/11/2011)[/b][hr]Sorry... I missed this... Maybe not in your country or language but that's not what's implied by such language in my country and the two statements have drastically different meanings. I actually take offense to the wording or would prefer that you not use it again and also change existing wording.[/quote]My apologies; I did not mean any offense. I've edited my code and post with the more precise version.That said, while I do not need to use the quirky update, did anyone ever notice what mistake I made on the 2 part version, so I can avoid making it again?Thu, 12 May 2011 13:40:57 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]ethan.murray (5/11/2011)[/b][hr]Thanks to all the contributors to this article and its comments. To help some purists understand how Jeff's article is adding value: I work in a management consulting firm where we use SQL as a more powerful version of Excel - very far from production, with custom-coded analyses we use for just a few months in a specific server environment, with the analyses carefully sanity checked by humans before implementing anything, and we just have to be right on average and not 100% of the time. A decade ago, one of us discovered quirky update (probably from one of the poorly written articles back then), and it has enabled us to run sophisticated analysis on multi-billion row tables to drive hundreds of millions of dollars of measurable value for our clients. Without quirky update, we would often not be able to deliver our answers quickly enough to drive this value. (Although I'm excited to try Paul's SQLCLR solution now, especially to see if it works or can be adapted to work for partitioned tables.)Over the years, we discovered many of these rules (most importantly, the row-increment error checking that I think Paul and Tom posted here), but it has been very helpful to have them all in one place! For the last year we have been referring our new analysts to this article as a helpful checklist to accompany the careful sanity checking and sample testing they always do when writing new code, and we have been so grateful to have it.For the forthcoming rewrite, it might be helpful to note (in an appendix?) that if you set the value of the same variable multiple times, it is not predictable in which order these statements will be processed. I know that should be the default assumption, and it has been pointed out in some of the earlier posts in this conversation. Still, it's something we've found worth including in our internal training documents because it often trips up newcomers to the technique and costs them some debugging time to learn it the hard way.[/quote]Ethan... thank you very much for taking the time to write that up. I appreciate it very much.Wed, 11 May 2011 17:05:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]Nadrek (4/12/2011)[/b][hr][quote][b]Jeff Moden (4/12/2011)[/b][hr]"Do not use this for critical things" is an "advertisement"? Not where I come from. ;-)And, yes, I use it for "critical" things.Anyway, sorry about the confusion on the clustered index. I got a bit cross-eyed on that because of the names of the tables. I'll take another look.[/quote]Well, the link to the article is an advertisement :). The "do not use this for critical things" is much easier to say than "you have to follow all the rules; if you fail even one rule, you may, or may not, receive invalid results"; and I tend towards limited use of undocumented features for critical work; I'll use them more often in noncritical work.[/quote]Sorry... I missed this... Maybe not in your country or language but that's not what's implied by such language in my country and the two statements have drastically different meanings. I actually take offense to the wording or would prefer that you not use it again and also change existing wording.Wed, 11 May 2011 17:04:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxThanks to all the contributors to this article and its comments. To help some purists understand how Jeff's article is adding value: I work in a management consulting firm where we use SQL as a more powerful version of Excel - very far from production, with custom-coded analyses we use for just a few months in a specific server environment, with the analyses carefully sanity checked by humans before implementing anything, and we just have to be right on average and not 100% of the time. A decade ago, one of us discovered quirky update (probably from one of the poorly written articles back then), and it has enabled us to run sophisticated analysis on multi-billion row tables to drive hundreds of millions of dollars of measurable value for our clients. Without quirky update, we would often not be able to deliver our answers quickly enough to drive this value. (Although I'm excited to try Paul's SQLCLR solution now, especially to see if it works or can be adapted to work for partitioned tables.)Over the years, we discovered many of these rules (most importantly, the row-increment error checking that I think Paul and Tom posted here), but it has been very helpful to have them all in one place! For the last year we have been referring our new analysts to this article as a helpful checklist to accompany the careful sanity checking and sample testing they always do when writing new code, and we have been so grateful to have it.For the forthcoming rewrite, it might be helpful to note (in an appendix?) that if you set the value of the same variable multiple times, it is not predictable in which order these statements will be processed. I know that should be the default assumption, and it has been pointed out in some of the earlier posts in this conversation. Still, it's something we've found worth including in our internal training documents because it often trips up newcomers to the technique and costs them some debugging time to learn it the hard way.Wed, 11 May 2011 15:42:51 GMTethan.murrayhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]Jeff Moden (4/12/2011)[/b][hr]"Do not use this for critical things" is an "advertisement"? Not where I come from. ;-)And, yes, I use it for "critical" things.Anyway, sorry about the confusion on the clustered index. I got a bit cross-eyed on that because of the names of the tables. I'll take another look.[/quote]Well, the link to the article is an advertisement :). The "do not use this for critical things" is much easier to say than "you have to follow all the rules; if you fail even one rule, you may, or may not, receive invalid results"; and I tend towards limited use of undocumented features for critical work; I'll use them more often in noncritical work.Tue, 12 Apr 2011 12:11:31 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]Nadrek (4/11/2011)[/b][hr]No, I'm not trolling; I'm advertising your article! The full context was:[code]-- Quirky update!!! BEWARE!!! Do not use this for critical things!-- BEFORE USING, read http://www.sqlservercentral.com/articles/T-SQL/68467/[/code][/quote]"Do not use this for critical things" is an "advertisement"? Not where I come from. ;-)And, yes, I use it for "critical" things.Anyway, sorry about the confusion on the clustered index. I got a bit cross-eyed on that because of the names of the tables. I'll take another look.Tue, 12 Apr 2011 12:01:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxNo, I'm not trolling; I'm advertising your article! The full context was:[code]-- Quirky update!!! BEWARE!!! Do not use this for critical things!-- BEFORE USING, read http://www.sqlservercentral.com/articles/T-SQL/68467/[/code][quote][b]Jeff Moden (4/8/2011)[/b][hr]Also, be advised. You didn't add the required clustered index to the #Temp table and that may be why your 2 part code didn't work the way you expected. Still and like I said before, a "Quirky Update" isn't need for the simple chore of diffing two adjacent rows. I'll have some code for you for that soon.[/quote]What is the problem with creating the clustered index as the primary key in the CREATE TABLE #tmp_conf_server_Inc statement itself, which was:[code] CONSTRAINT [PK_cls_RecordedDateTime_Servername__#tmp_conf_server_Inc] PRIMARY KEY CLUSTERED( [Anchor], [ServerName] ASC, [RecordedDateTime] ASC)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE) ON [PRIMARY][/code]Was there another problem with my two-part set statement variant?I see the Rownum based self-join; I'll take a look at how that performs with several hundred thousand rows when I get a chance.Mon, 11 Apr 2011 09:56:21 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxThey in the incorrect order only on the source table. That's why he copies the rows to another table where he can apply a different clustered index.Still, you just don't need a QU for this exercise.Sun, 10 Apr 2011 11:51:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspx[quote][b]Jeff Moden (4/8/2011)[/b][hr]Also, be advised. You didn't add the required clustered index to the #Temp table and that may be why your 2 part code didn't work the way you expected. Still and like I said before, a "Quirky Update" isn't need for the simple chore of diffing two adjacent rows. I'll have some code for you for that soon.[/quote]Nadrek,In case you're wondering, and if I'm not mistaken, the clustered index fields are in the wrong order.Sun, 10 Apr 2011 01:16:51 GMTLangston Montgomeryhttp://www.sqlservercentral.com/Forums/Topic802558-203-1.aspxHere's the non-Quirky Update code to do what you want. For those that think a CTE would be better, please read the comments in the code to find out why you might want to reconsider...[code="sql"]--=====================================================================================================================-- Setup the demonstration. Nothing in this section is a part of the solution. It's just to create test data.--=====================================================================================================================--===== Do this demonstration in a nice safe place that everyone has. USE tempdb;--===== Conditionally drop all the test tables to make reruns easier IF OBJECT_ID('tempdb.dbo.tmp_conf_server' ,'U') IS NOT NULL DROP TABLE tempdb.dbo.tmp_conf_server;--===== Create a simulation of a real table in tempdb CREATE TABLE dbo.tmp_conf_server ( RecordedDateTime DATETIME NOT NULL, ServerName VARCHAR(260) NOT NULL, LastRestarted DATETIME NOT NULL, TotalBatchRequests BIGINT NOT NULL, TotalDeadlocks BIGINT NOT NULL, TotalCompilations BIGINT NOT NULL, TotalReCompilations BIGINT NOT NULL, CONSTRAINT PK_cls_RecordedDateTime_Servername__tmp_conf_server PRIMARY KEY CLUSTERED (RecordedDateTime ASC, ServerName ASC) );--===== Populate the simulated real table with test data INSERT INTO tempdb.dbo.tmp_conf_server (RecordedDateTime, ServerName, LastRestarted, TotalBatchRequests, TotalDeadlocks, TotalCompilations, TotalReCompilations) SELECT '2011-04-05 16:46:46.630','server1','2011-02-25 13:29:00.000',500,12,3050,5555 UNION ALL SELECT '2011-04-05 17:49:22.350','server1','2011-02-25 13:29:00.000',750,12,4000,6666 UNION ALL SELECT '2011-04-06 17:34:22.473','server1','2011-02-25 13:29:00.000',810,12,4075,7777 UNION ALL SELECT '2011-04-05 16:46:47.630','server2','2011-02-27 11:29:00.000',888,5 ,30 ,3333 UNION ALL SELECT '2011-04-05 17:49:23.350','server2','2011-02-27 11:29:00.000',891,6 ,40 ,5900 UNION ALL SELECT '2011-04-06 17:34:23.473','server2','2011-02-27 11:29:00.000',950,8 ,7000,5950;--=====================================================================================================================-- Solve the problem of getting a "delta" between the current and previous rows for the "INC" columns-- in a simplified manner and without a "Quirky Update".--=====================================================================================================================--===== Conditionally drop all the test tables to make reruns easier IF OBJECT_ID('tempdb..#tmp_conf_server_Inc','U') IS NOT NULL DROP TABLE #tmp_conf_server_Inc;--===== Create and populate the table we'll use to calculate the increments with. -- Notice how ISNULL is used to make a NOT NULL column on the fly. SELECT RowNum = ISNULL(ROW_NUMBER() OVER (ORDER BY ServerName, RecordedDateTime),0), ServerName, RecordedDateTime, LastRestarted, TotalBatchRequests, TotalDeadlocks, TotalCompilations, TotalReCompilations, IncSeconds = CAST(0 AS BIGINT), IncBatchRequests = CAST(0 AS BIGINT), IncDeadlocks = CAST(0 AS BIGINT), IncCompilations = CAST(0 AS BIGINT), IncReCompilations = CAST(0 AS BIGINT) INTO #tmp_conf_server_Inc FROM dbo.tmp_conf_server;--===== Add a clustered index (as a PKK in this case) NOT to do a "Quirky Update" but to simply make the code run faster. -- As a sidebar, NEVER name a constraint on a #Temp table or it will destroy the ability to do concurrent runs. ALTER TABLE #tmp_conf_server_Inc ADD PRIMARY KEY CLUSTERED (RowNum);--===== You could certainly do this without a Temp Table using a CTE, but it would take twice as long because it would -- have to execute the CTE twice. SELECT hi.ServerName, hi.RecordedDateTime, hi.LastRestarted, hi.TotalBatchRequests, hi.TotalDeadlocks, hi.TotalCompilations, hi.TotalReCompilations, IncSeconds = CASE WHEN hi.ServerName = lo.ServerName THEN DATEDIFF(ss, lo.RecordedDateTime , hi.RecordedDateTime) ELSE NULL END, IncBatchRequests = CASE WHEN hi.ServerName = lo.ServerName THEN hi.TotalBatchRequests - lo.TotalBatchRequests ELSE NULL END, IncDeadlocks = CASE WHEN hi.ServerName = lo.ServerName THEN hi.TotalDeadlocks - lo.TotalDeadlocks ELSE NULL END, IncCompilations = CASE WHEN hi.ServerName = lo.ServerName THEN hi.TotalCompilations - lo.TotalCompilations ELSE NULL END, IncReCompilations = CASE WHEN hi.ServerName = lo.ServerName THEN hi.TotalReCompilations - lo.TotalReCompilations ELSE NULL END FROM #tmp_conf_server_Inc lo RIGHT OUTER JOIN #tmp_conf_server_Inc hi ON lo.RowNum+1 = hi.RowNum;[/code]Fri, 08 Apr 2011 20:50:59 GMTJeff Moden