InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 22:41:03 GMT20http://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]Jeff Moden (5/3/2009)[/b][hr]Thanks for the offers of help guys. I love this team.I've got 2k Dev, 2k5 Dev, and I'll soon have 2k8 Dev. I may also be shifting primary computers... the one I have is rock solid and trustworthy but it's also 7 years old and isn't portable. Looks like I have to break down and buy a laptop. Probably won't go with Vista because it's my understanding that Vista won't support 2k and I still need to support 2k.[/quote]Yeah I gave in on the Laptop as primary thing two years ago. It's really great for me as a roaming consultant though, no matter where I am, I always have all of my stuff with me.One thing that I overlooked at first though: good backups. They are even more important when I have everything on one box, plus the wear and tear of startup/shutdown & transportation puts that much more stress on the disks. Fortunately, USB drives are cheap and many come with Backup software (Maxtor's are pretty good). So now I just have a pair of 250GB's beside my desk at home that I attach at night to do the overnight backups.Sun, 03 May 2009 13:16:26 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxThanks for the offers of help guys. I love this team.I've got 2k Dev, 2k5 Dev, and I'll soon have 2k8 Dev. I may also be shifting primary computers... the one I have is rock solid and trustworthy but it's also 7 years old and isn't portable. Looks like I have to break down and buy a laptop. Probably won't go with Vista because it's my understanding that Vista won't support 2k and I still need to support 2k.Sun, 03 May 2009 09:09:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxHey Jeff... If you need to test anything, I've got 2005 & 2008 Dev Editions on my laptop.Sun, 03 May 2009 06:27:34 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxJeff: I also have SQL Server 2008 Developer Edition on my laptop if you need it.Sun, 03 May 2009 00:42:15 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxIndeed.The prerequisite is you need the clustered index organized for your processing needs ( AccountID, [Date] in the example ).Any other organisation will cause the mechanisme to fail.I tested multiple compositions of clix and NCI (on AccountID, [Date]) and couldn't push it to work correct. (Only clix on AccountID, [Date] gives the correct results)Tue, 28 Apr 2009 00:46:49 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxJohan,Run the first test using the clustered index as it is. Then, drop the clustered index and add a new one based on some other columns. Run your ORDER BY code and do the compare. I believe you'll find it fails to work as advertised and that's part of the reason why I'm rewriting the article.Mon, 27 Apr 2009 19:12:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxhmm ....I ran this comparisson on Microsoft SQL Server 2008 ([b]SP1[/b]) - 10.0.2531.0 (Intel [b]X86[/b]) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation [b]Developer Edition [/b]on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)(32-bit / 1 proc / 4 cores / 4Gb ram / ([u]/3GB[/u] not enabled) )(4 files for tempdb)[code]/* set statistics io on set statistics time on*/-- JBMTest and JBMTest2 are exact copies ! of the original test table./* reset data *//* UPDate dbo.JBMTest SET RunBal = NULL, GrpBal = NULL , RunCnt = NULL , GrpCnt = NULL UPDate dbo.JBMTest2 SET RunBal = NULL, GrpBal = NULL , RunCnt = NULL , GrpCnt = NULL*/--go/*ALTER INDEX [IX_JBMTest_AccountID_Date] ON [dbo].[JBMTest] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )*//*ALTER INDEX [IX_JBMTest2_AccountID_Date] ON [dbo].[JBMTest2] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )*/-- NOW THE TESTdeclare @tsb as datetimeset @tsb = getdate()print '** Begin ' + convert(char(26),@tsb,21) + ' Begin **'--===== Declare the variables for the "Code Basis"DECLARE @PrevRunBal MONEY --Overall running total SET @PrevRunBal = 0DECLARE @PrevGrpBal MONEY --Running total resets when account changes SET @PrevGrpBal = 0DECLARE @PrevRunCnt INT --Overall running count (ordinal rank) SET @PrevRunCnt = 0DECLARE @PrevGrpCnt INT --Running count resets when account changes SET @PrevGrpCnt = 0DECLARE @PrevAcctID INT --The "anchor" and "account change detector" SET @PrevAcctID = 0--===== Solve 2 types of Running Total and 2 types of Running Count problems -- using a single update based on a Clustered Index at VERY high speeds. UPDATE T SET --===== Running Total @PrevRunBal = RunBal = @PrevRunBal + Amount, --===== Grouped Running Total (Reset when account changes) @PrevGrpBal = GrpBal = CASE WHEN AccountID = @PrevAcctID THEN @PrevGrpBal + Amount ELSE Amount -- Restarts total at "0 + current amount" END, --===== Running Count (Ordinal Rank) @PrevRunCnt = RunCnt = @PrevRunCnt + 1, --===== Grouped Running Total (Ordinal Rank, Reset when account changes) @PrevGrpCnt = GrpCnt = CASE WHEN AccountID = @PrevAcctID THEN @PrevGrpCnt + 1 ELSE 1 -- Restarts count at "1" END, --===== "Anchor" and provides for "account change detection" @PrevAcctID = AccountID FROM dbo.JBMTest T WITH (INDEX(IX_JBMTest_AccountID_Date),TABLOCKX)/* SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table 'JBMTest'. Scan count 1, logical reads 7222, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 15140 ms, elapsed time = 28854 ms.*/ declare @tse as datetimeset @tse = current_timestampprint '** End ' + convert(char(26),@tse,21) + ' End ** - elaps ms: ' + convert(char(26),datediff(ms,@tsb, @tse))godeclare @tsb as datetimeset @tsb = getdate()print '** Begin P2 ' + convert(char(26),@tsb,21) + ' Begin **'--===== Declare the variables for the "Code Basis"DECLARE @PrevRunBal MONEY --Overall running total SET @PrevRunBal = 0DECLARE @PrevGrpBal MONEY --Running total resets when account changes SET @PrevGrpBal = 0DECLARE @PrevRunCnt INT --Overall running count (ordinal rank) SET @PrevRunCnt = 0DECLARE @PrevGrpCnt INT --Running count resets when account changes SET @PrevGrpCnt = 0DECLARE @PrevAcctID INT --The "anchor" and "account change detector" SET @PrevAcctID = 0--===== Solve 2 types of Running Total and 2 types of Running Count problems -- using a single update based on a Clustered Index at VERY high speeds. UPDATE T SET --===== Running Total @PrevRunBal = RunBal = @PrevRunBal + Amount, --===== Grouped Running Total (Reset when account changes) @PrevGrpBal = GrpBal = CASE WHEN AccountID = @PrevAcctID THEN @PrevGrpBal + Amount ELSE Amount -- Restarts total at "0 + current amount" END, --===== Running Count (Ordinal Rank) @PrevRunCnt = RunCnt = @PrevRunCnt + 1, --===== Grouped Running Total (Ordinal Rank, Reset when account changes) @PrevGrpCnt = GrpCnt = CASE WHEN AccountID = @PrevAcctID THEN @PrevGrpCnt + 1 ELSE 1 -- Restarts count at "1" END, --===== "Anchor" and provides for "account change detection" @PrevAcctID = AccountID FROM (select top 100 percent * from dbo.JBMTest2 order by AccountID, [Date] ) T -- option (maxdop 1)/* SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table 'JBMTest2'. Scan count 1, logical reads 7222, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 14156 ms, elapsed time = 36166 ms.(1000000 row(s) affected)*/declare @tse as datetimeset @tse = current_timestampprint '** End P2' + convert(char(26),@tse,21) + ' End ** - elaps ms: ' + convert(char(26),datediff(ms,@tsb, @tse))go SELECT * FROM dbo.JBMTest2 except Select * FROM dbo.JBMTest ORDER BY AccountID, Date/*SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.(0 row(s) affected)Table 'JBMTest2'. Scan count 5, logical reads 7288, physical reads 0, read-ahead reads 9, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'JBMTest'. Scan count 5, logical reads 7288, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 25955 ms, elapsed time = 7012 ms.*/go[/code]Note [i]the maxdop 1 was not needed to get the correct result.[/i]Mon, 27 Apr 2009 03:45:55 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxNo problem. Depending on when you get it done I may even have 2008 on a server that I can play with!Fri, 24 Apr 2009 18:17:10 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]Jack Corbett (4/24/2009)[/b][hr]Jeff, If you send me the test code I can run it in 2008. I have it on my laptop.[/quote]Very cool, Mr. Corbett. I'll take you up on that offer as soon as I finish testing on my end. Thank you for the "leg up".Fri, 24 Apr 2009 17:15:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxJeff, If you send me the test code I can run it in 2008. I have it on my laptop.Fri, 24 Apr 2009 16:17:35 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxThe good news will come when they finally get all of the windowing functions like SUM() OVER working correctly so we don't have to trick SQL server with things like quirky updates.Fri, 24 Apr 2009 15:42:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]Jeff Moden (4/24/2009)[/b][hr][quote][b]Lynn Pettis (4/24/2009)[/b][hr]Jeff, Do you have access to SQL Server 2008? At the SSWUGvConference I heard the the ORDER BY "may be" honored again in the UPDATE statement. I don't have SQL Server 2008 yet so I can't test it.[/quote]That's good news, but, no, I don't have 2k8, yet.[/quote]Well, it isn't good news until it is proven true. I'd call it hopeful news.Fri, 24 Apr 2009 14:06:36 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]Lynn Pettis (4/24/2009)[/b][hr]Jeff, Do you have access to SQL Server 2008? At the SSWUGvConference I heard the the ORDER BY "may be" honored again in the UPDATE statement. I don't have SQL Server 2008 yet so I can't test it.[/quote]That's good news, but, no, I don't have 2k8, yet.Fri, 24 Apr 2009 13:59:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxJeff, Do you have access to SQL Server 2008? At the SSWUGvConference I heard the the ORDER BY "may be" honored again in the UPDATE statement. I don't have SQL Server 2008 yet so I can't test it.Fri, 24 Apr 2009 13:21:01 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxUmmm.... no.... to be as safe as possible with this method, you MUST include a FROM clause in the update and it must use a TabLockX and index hint that forces the use of the clustered index.Wed, 08 Apr 2009 10:27:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxYES YES, it is working, had to make some minor alterations due to an extra dimension (dataareaid)Thanks a lot for the quick reply and the correct answer.I went from 4.5 hours to 7 !!!! seconds, talk about an improvement. I added my alteration in case any can use it againThanks again, and specially thanks to Jeff Moden witch is the originator of the codeKind regardsABB-- Original code from Jeff Moden alterated by ABBDECLARE @PrevCompany varchar(3) SET @PrevCompany = ''DECLARE @PrevCompanyBalance MONEY --running total for each company SET @PrevCompanyBalance = 0DECLARE @PrevGrpBal MONEY --Running total resets when account changes SET @PrevGrpBal = 0DECLARE @PrevRunCnt INT --Overall running count (ordinal rank) SET @PrevRunCnt = 0DECLARE @PrevGrpCnt INT --Running count resets when account changes SET @PrevGrpCnt = 0DECLARE @PrevAcctID varchar(10) --The "account change detector" SET @PrevAcctID = '' UPDATE dbo.DW_CustTrans SET --===== Running Total Company @PrevCompanyBalance = CompanyBalance = CASE WHEN dataareaid = @Prevcompany THEN @PrevCompanyBalance + Amount ELSE Amount END, --===== Account Running Total (Reset when account changes) @PrevGrpBal = Balance_MST = CASE WHEN accountnumber = @PrevAcctID THEN @PrevGrpBal + Amount ELSE Amount -- Restarts total at "0 + current amount" END, --===== Running Count (Ordinal Rank) @PrevRunCnt = RunCnt = @PrevRunCnt + 1, --===== Account Running Total (Ordinal Rank, Reset when account changes) @PrevGrpCnt = CompanyCount = CASE WHEN accountnumber = @PrevAcctID THEN @PrevGrpCnt + 1 ELSE 1 -- Restarts count at "1" END, --===== "Anchor" and provides for "account and Company change detection" @PrevAcctID = accountnumber, @prevCompany = dataareaid FROM dbo.DW_CustTrans WITH (INDEX(IX_DW_CustTrans),TABLOCKX)Wed, 08 Apr 2009 03:40:15 GMTabb-1001266http://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]LeeBear35 (1/30/2009)[/b][hr]I have encountered problems with Merry Go Round issues too. On small samples it would always work but as data sets grew it was more and more of an issue then I realized that even with a clustered index if you have an 80% fill factor the page is only filled with the first 6K of data then goes to the next page. This happens until all the currently allocated pages in the table are are filled to 80%, then the remaining 20% is filled then a new set of pages are allocated. I have often found a clustered index with a WITH Fillfactor=100 prevents this issue, but does not always resolve the problem.Happy hunting...[/quote]Please see the post just above this one for an "explanation" of what happened.Fri, 30 Jan 2009 20:36:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]Martin Stephenson (1/30/2009)[/b][hr]Great article, I 've been able to apply it to a pain in the neck I've been working on, but at the risk of looking like a thicko --:D I cant get the order by update to work with a non clustered index...... I looked at this and thought it was giving me the wrong results because it is scanning the clustered PK on both tables before the join and therfore is getting the rownum order so I changed the join to 'ON t1.accountID = d.accountID and t1.date = d.date' and the query plan now shows a scan on the index of the subquery and gives me the right results,apart from one mention on the first page of the discussion 200 or so posts ago no one else seems to have had this problem, or am I missing the point of the merry go round index:unsure:[/quote]Oh my my my [font="Arial Black"]MY![/font] The irony of it all! :D:P:hehe: In the words of a very wise man, "The Lord giveth, and Microsoft taketh away! ;)Everyone kept busting my hump about how the "quirky" update was undocumented and could "change at the next service pack" and insisted that I use an ORDER BY, which I did. Here's the really funny part... the ORDER BY update used to work just fine when I wrote this article. I hadn't yet installed SQL Server 2005. I was still working on just SQL Server 2000 sp3a (I didn't trust sp4 so never installed it) and it worked just fine... then, I never used the ORDER BY method ever again because, frankly, it was too slow for me. Since that time, I've installed SQL Server 2005 and sp2... now, [i]NONE of the original ORDER BY code works [/i]on my box either in 2k or 2k5 with or without the additional index! Even the mods you put on the code don't work on my box even though you say it does on your box. (I wouldn't use the ORDER BY method if I were you :))The irony is that such a well documented feature as ORDER BY changed without warning and the "undocumented" feature keeps right on working as advertised.I'll make a note of this at the start of the thread so people are made aware. It also gives me incentive to expedite the rewrite I'm currently working on for the article.Thanks for bringing this to my attention!Fri, 30 Jan 2009 20:35:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxI have encountered problems with Merry Go Round issues too. On small samples it would always work but as data sets grew it was more and more of an issue then I realized that even with a clustered index if you have an 80% fill factor the page is only filled with the first 6K of data then goes to the next page. This happens until all the currently allocated pages in the table are are filled to 80%, then the remaining 20% is filled then a new set of pages are allocated. I have often found a clustered index with a WITH Fillfactor=100 prevents this issue, but does not always resolve the problem.Happy hunting...Fri, 30 Jan 2009 06:42:05 GMTLeeBear35http://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxThanks for the detailed explanation... I'll take a look tonight after I get home.Fri, 30 Jan 2009 06:04:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxGreat article, I 've been able to apply it to a pain in the neck I've been working on, but at the risk of looking like a thicko --:D I cant get the order by update to work with a non clustered index If I run the following code--===== Add the primary key ALTER TABLE dbo.JBMTest ADD PRIMARY KEY CLUSTERED (RowNum) --clustered for "Merry-go-Round" test--===== Add the "sorting index" to the table CREATE NONCLUSTERED INDEX IX_JBMTest_AccountID_Date --not clustered for "Merry-go-Round" test ON dbo.JBMTest (AccountID, Date)--===== Build the table 100 rows at a time to "mix things up"DECLARE @Counter INT SET @Counter = 0 WHILE @Counter &lt; 1000000 BEGIN --===== Add 1000 rows to the test table INSERT INTO dbo.JBMTest (AccountID, Amount, Date) SELECT TOP 100 AccountID = ABS(CHECKSUM(NEWID()))%50000+1, Amount = CAST(CHECKSUM(NEWID())%10000 /100.0 AS MONEY), Date = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME) FROM Master.dbo.SysColumns t1 CROSS JOIN Master.dbo.SysColumns t2 --===== Increment the counter SET @Counter = @Counter + 100 END--===== Declare the required variablesDECLARE @PrevBal MONEY SET @PrevBal = 0DECLARE @AccountID INT SET @AccountID = 0--===== Calculate the running total using SQL Server's proprietary update -- and force the order of the update with and ORDER BY UPDATE t1 SET @PrevBal = RunBal = t1.Amount + @PrevBal, @AccountID = t1.AccountID --This does nothing but provide an "anchor" FROM dbo.JBMTest t1 INNER JOIN (--==== Derived table "d" provides the sort order for the update SELECT TOP 100 PERCENT rownum,AccountID, Date FROM dbo.JBMTest ORDER BY AccountID, Date )d ON t1.rownum = d.rownumI get a very quick update based on rownum order, I 'va attached the execution plan I looked at this and thought it was giving me the wrong results because it is scanning the clustered PK on both tables before the join and therfore is getting the rownum order so I changed the join to 'ON t1.accountID = d.accountID and t1.date = d.date' and the query plan now shows a scan on the index of the subquery and gives me the right results,apart from one mention on the first page of the discussion 200 or so posts ago no one else seems to have had this problem, or am I missing the point of the merry go round index:unsure:Fri, 30 Jan 2009 03:56:38 GMTMartin Stephensonhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]LeeBear35 (1/16/2009)[/b][hr]As like the solution using the Anchor value, I have not seen that "fix" before. I also wondered if the clustered index was generated with a fillfactor=100 is the anchor still needed?Also, do you think the will ever the ability to do computations in a SELECT statement like those shown on the UPDATE statement. If they did then the solution could be as simple as:[code]use [NorthWind]goDECLARE @SumFreight AS MONEYDECLARE @CntFreight AS INTSELECT @SumFreight = 0.0, @CntFreight = 0SELECT [OrderID], [Freight], @SumFreight = [RunningTotal] = @SumFreight + ISNULL([Freight], 0.0), @CntFreight = [RunningCount] = @CntFreight + 1FROM [dbo].[Orders]ORDER BY [OrderId][/code][/quote]I absolutely agree... Wouldn't THAT be wonderful?! That would solve so very many problems!Sat, 17 Jan 2009 15:28:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxYou can do the computations in a SELECT statement, you just cannot assign to both variables and columns in a select statement.Shame too.Fri, 16 Jan 2009 11:48:21 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxAs like the solution using the Anchor value, I have not seen that "fix" before. I also wondered if the clustered index was generated with a fillfactor=100 is the anchor still needed?Also, do you think the will ever the ability to do computations in a SELECT statement like those shown on the UPDATE statement. If they did then the solution could be as simple as:[code]use [NorthWind]goDECLARE @SumFreight AS MONEYDECLARE @CntFreight AS INTSELECT @SumFreight = 0.0, @CntFreight = 0SELECT [OrderID], [Freight], @SumFreight = [RunningTotal] = @SumFreight + ISNULL([Freight], 0.0), @CntFreight = [RunningCount] = @CntFreight + 1FROM [dbo].[Orders]ORDER BY [OrderId][/code]Fri, 16 Jan 2009 11:42:38 GMTLeeBear35http://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxAgreed not RBAR, but RBR LOL:DMon, 22 Dec 2008 07:00:03 GMTAnzioBakehttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]AnzioBake (12/22/2008)[/b][hr]What happens for for tables that the rows are not in a clustered index with the right order.[/quote]Like I said in the article... copy to a temp table and index that. It's still faster than using a cursor.Mon, 22 Dec 2008 05:14:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxI agree... this one looks like RBAR because it does something for each row... but so do all of the queries built in SQL Server. The difference here is we're letting the optimizer handle the update of rows using a single query instead of a declared loop or a triangular join.In fact, some folks call this method a "pseudo-cursor" and that's an appropriate name for it. So it might be RBR... but if you consider the speed, it's definitely not RBAR.Mon, 22 Dec 2008 05:12:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxHi.Firstly I am not trying to dispute the actual solution you are providing. In fact, I came across the phenomenon several years ago (by accident) but never found a use for it because of the ordering issue.....I am fully aware of the "internal Row" produced by "triangular" joins" and usually include some coding for optimization, and prefer code that limits the number of internal rows.I find it interesting that this relise on a clustered index. What happens for for tables that the rows are not in a clustered index with the right order.What I REALLY question though, is that you suggest this solution instead of "triangular joins" as TJ's are "hidden RBAR" when this solution uses exactly that....RBAR. It realise on the Variable being updated "row by agonizing row"As I said. This is not to dispute the Solution just the Definition.....Mon, 22 Dec 2008 01:24:08 GMTAnzioBakehttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxDang it... I don't know how I missed your latest post on this, Lynn.Heh... yeah... the concept can be used for a huge number of things. I remember seeing that post and a whole bunch of others where folks used this concept to just blow away WHILE loops of any kind.Thanks for the feedback, Lynn!Tue, 16 Dec 2008 21:26:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxJust used some of the concepts behind running totals to help eliminate RBAR in another post. The concept used here isn't just useable for running totals. It can also be used to capture intermediary results that need to be used in calculations "later" in the update.Amazing what I have picked up here! Thanks, Jeff!Tue, 25 Nov 2008 07:15:28 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]mstjean (11/21/2008)[/b][hr][i][b]*Wow*[/b][/i]I just found this article and cannot wait to run some tests and eventually try it out in prod. I've only read the first page of comments, so no doubt someone has already checked this code in SQL 2k8 (not running it yet, but...).It's not often I find "a whole new way" to get from a to b (as fast as possible)... thanks again.[/quote]Absolutely my pleasure. Thanks for the feedback. If you get a chance, post your "post test" feedback. I've obviously got a vested interest on any testing folks do on this. Thanks, again.Fri, 21 Nov 2008 19:14:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]Jeff Moden (1/31/2008)[/b][hr][quote][b]James Goodwin (1/31/2008)[/b][hr]Did you test using the Clustered Index with the ORDER BY subquery? It seems to me that the ORDER BY Would enforce the order, but since you are using a Clustered Index it wouldn't cause much overhead unless you were going to get bit by a disordered resultset anyway.--JimFive[/quote]No... I didn't test for that and the article got a wee bit long anyway. Was really trying to show that either an Order By (regardless of what the Clustered Index was) would work or that a Clustered Index on the correct columns would work.Thanks for the feedback and the suggestion, Jim. I'll give it a try a post the results...[/quote]Sorry for the very long delay... I ran the "Order By" example from the article without the clustered index... as expected, a "Sort" icon appeared in the Execution Plan. Then, I added the clustered index without changing the code a bit... the "Sort" icon went away. Apparently, SQL Server also knows it doesn't need to sort when the clustered index (ie, the table) goes through a whole table scan as that which the update causes.By the way... just to make something perfectly clear (we all learn something as time passes by)... the Index Hint used in the article will NOT guarantee the order of a [i]Select[/i]... you must use an Order By for that. But, it does guarantee the order in an Update as used. If anyone still has doubts, then go ahead an use the Order By method... with or without the clustered index (which made virtually no difference in speed because of full index scan), it's still faster than a Cursor, a While Loop, or a Triangular Join. As a sidebar, I reran the indes hint code with only 100 accounts across the million rows to guarantee some pretty nasty page splits... the verification code still shows no errors.Fri, 21 Nov 2008 19:10:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]Christopher Ford (5/11/2008)[/b][hr][quote][b]Anirban Paul (5/11/2008)[/b][hr]Abosolutely. What I like most about the discussions is that you can't just write 'casual' writing. You have to be absolutely sure about the content before putting it. So we are getting very good stuffs on this forum.:)[/quote]I agree with you...I posted a solution I thought was better than Jeff's one time...turned out that was precisely what he was explaining in his article on trying to avoid...Doh! :hehe:[/quote]Heh... just a reality check... I'm not always right :blush:, but THAT is precisely what makes forums so very powerful... especially this one. Most folks that take the time to challenge something are good, well mannered professionals that seem to ALWAYS look for a better way. In the process, a better way frequently comes to the surface or a darned good reason not to do something a certain way does. Everybody learns through such dialog.Thanks for the great posts, Christopher. And, I'm not talking about just this one. :)p.s. I'm truly sorry for the late response... just noticed your post was from 5/11. Pretty much delinquent for me.Fri, 21 Nov 2008 17:48:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[i][b]*Wow*[/b][/i]I just found this article and cannot wait to run some tests and eventually try it out in prod. I've only read the first page of comments, so no doubt someone has already checked this code in SQL 2k8 (not running it yet, but...).It's not often I find "a whole new way" to get from a to b (as fast as possible)... thanks again.Fri, 21 Nov 2008 10:43:18 GMTmstjeanhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]Anirban Paul (5/11/2008)[/b][hr]Abosolutely. What I like most about the discussions is that you can't just write 'casual' writing. You have to be absolutely sure about the content before putting it. So we are getting very good stuffs on this forum.:)[/quote]I agree with you...I posted a solution I thought was better than Jeff's one time...turned out that was precisely what he was explaining in his article on trying to avoid...Doh! :hehe:Sun, 11 May 2008 20:13:04 GMTChristopher Ford-327546http://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxAbosolutely. What I like most about the discussions is that you can't just write 'casual' writing. You have to be absolutely sure about the content before putting it. So we are getting very good stuffs on this forum.:)Sun, 11 May 2008 09:47:32 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxThanks, Anirban!I absolutely agree. Steve Jones recently wrote an article asking the quesiton "Why do you write"? One of the reasons I pointed out was the incredible amount of discussion that occurs after the article. We've got folks that did some performance tests and shared the code... we've got folks that suggest alternatives and shared the code... we've got folks that explained how they used something and, perhaps, the success story that followed. And, we 've got folks that politely (or not) disagree with the article and that spurs on even more discussion.And, it's all good... makes everyone think and try new things. Even a couple of related (or not) sidebars show up and more discussion occurs.You've just gotta love all the folks that take the time to get involved in these discussions! I probably learn more from the discussions than what I learn from the orginal article... even if I'm the one that wrote the article! :DSun, 11 May 2008 09:36:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxI picked these link from another discussion. Your articles are really good. What is more interesting is the discussion that follows your articles. Amazing. :)Sun, 11 May 2008 09:10:35 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx[quote][b]srienstr (4/1/2008)[/b][hr]Ridiculously late addendum, but [url="http://www.cutepdf.com/"]Cute-PDF[/url] is free and has worked wonderfully for me so far, at least with things that are properly formatted for printing, it chokes a bit on printing webpages.[/quote]Ridiculously [i]useful [/i]addendum, too! :P Thanks for the tip, Srienstr!Tue, 01 Apr 2008 11:38:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic449802-203-1.aspxRidiculously late addendum, but [url="http://www.cutepdf.com/"]Cute-PDF[/url] is free and has worked wonderfully for me so far, at least with things that are properly formatted for printing, it chokes a bit on printing webpages.Tue, 01 Apr 2008 11:16:24 GMTsrienstr