InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 13:49:02 GMT20http://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxLynn,I can't believe that I missed this article when it came out. This is a fantastic article that I've put into my briefcase.Fantastic job.Wed, 03 Jun 2009 17:30:30 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]Lynn Pettis (4/27/2009)[/b][hr][quote][b]ALZDBA (4/27/2009)[/b][hr]As you suggested the "Order by" testing with Jeffs solution, I also tested your marvellous solution for partitioned objects.First findings .... [b]It no longer works [/b].... SQL2008 ( sp1 ) is getting smarter. The more things we try to fool it, it just ignores them.[i]They must have a huge pile of fools, to anticipate all the foolish things we try :-D [/i] (forcing a sets processing in a certain order.)I'm still trying to figure out why ..... or why not.[/quote]Okay, I really need to get SQL Server 2008 Developers Edition. Have you tested the ORDER BY on the partitioned table itself?[/quote]Lynn, try [url=http://www.amazon.com/gp/product/B001B8EZR4]here[/url]. $45 is pretty good, and because I'm an Amazon Prime customer, I get the 2-day shipping free.Sun, 03 May 2009 00:55:56 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxActually, it only looked like the ORDER BY method worked in my article. It wasn't the ORDER BY that made it work. Of course, I'm just a day or two from submitting the article for republishing and I don't want to give away too much thunder. :-)Mon, 27 Apr 2009 19:16:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]ALZDBA (4/27/2009)[/b][hr]As you suggested the "Order by" testing with Jeffs solution, I also tested your marvellous solution for partitioned objects.First findings .... [b]It no longer works [/b].... SQL2008 ( sp1 ) is getting smarter. The more things we try to fool it, it just ignores them.[i]They must have a huge pile of fools, to anticipate all the foolish things we try :-D [/i] (forcing a sets processing in a certain order.)I'm still trying to figure out why ..... or why not.[/quote]Okay, I really need to get SQL Server 2008 Developers Edition. Have you tested the ORDER BY on the partitioned table itself?Mon, 27 Apr 2009 07:48:20 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxAs you suggested the "Order by" testing with Jeffs solution, I also tested your marvellous solution for partitioned objects.First findings .... [b]It no longer works [/b].... SQL2008 ( sp1 ) is getting smarter. The more things we try to fool it, it just ignores them.[i]They must have a huge pile of fools, to anticipate all the foolish things we try :-D [/i] (forcing a sets processing in a certain order.)I'm still trying to figure out why ..... or why not.Mon, 27 Apr 2009 07:01:33 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]Grant Fritchey (1/27/2009)[/b][hr]Uh... Wow... Over-achieve much?[/quote]Ha! This coming from a guy who wrote 180 pages on execution plans. And Lynn, I concur with all of the above who congratulated your article, very nice writing and explanation of the process.Wed, 25 Feb 2009 11:55:07 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]RBarryYoung (2/21/2009)[/b][hr]You know, I've been so busy the last month or so, I missed this when it first came out. This is an impressive piece of work, Lynn, congratulations. I am sure that I will be referring to it in the future.[/quote]Thank you. :blush:I do have to give credit to Jeff for the groundwork he laid in his article, and to Gail for the idea based on her comment on Jeff's work about the "quirky update" not working with partitioned tables. That was my incentive to find a viable solution.Sat, 21 Feb 2009 21:41:56 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxYou know, I've been so busy the last month or so, I missed this when it first came out. This is an impressive piece of work, Lynn, congratulations. I am sure that I will be referring to it in the future.Sat, 21 Feb 2009 20:42:13 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]Paul DB (1/28/2009)[/b][hr]Wooh ... this is some article. Great job! :cool:[/quote]Thank you. :blush:Wed, 28 Jan 2009 11:00:24 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxWooh ... this is some article. Great job! :cool:Wed, 28 Jan 2009 10:53:45 GMTPaul DBhttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxI didn't test Jeff's code against just one partition, but from what I saw of the data when run against the whole table, I'd have to say that in that case the results may be what you would be looking for. This would be a good test for someone to try.It may be a while before I can, as I have some other things going on right now outside of work that are taking up quite a bit of my free time.Wed, 28 Jan 2009 10:30:52 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxYeah if you could restrict the rows (and columns if savings is alot) it would be more reasonable. None of your code had WHERE clauses so I assumed that you wanted to crunch the whole table. If there was a WHERE clause, does Jeff's version break even if the qualifying rows don't span partitions?Wed, 28 Jan 2009 10:23:22 GMTYeshuaAgapaohttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]YeshuaAgapao (1/28/2009)[/b][hr]Makes sense now. I would still suggest that if the running totals requirement was the dominant form of querying on the table, I would rather just un-partition the whole table rather than create a non-partitioned duplicate of it (temporarily with the temp table or worse, permanently with the non-paritioned indexed view). Otherwise I would really try not to fulfill any kind of realtime reporting requirement on it, only batch pre-generation of the aggregates.[/quote]I can't agree with un-partitioning the table. There may be other reasons that the table is partitioned. One reason, perhaps it isn't one million rows of data over ten years but 1 billion rows of data over ten years. With that, the majority of the queries against the table are limited to the current year and maybe the previous year with less than five percent of all queries access data older than that, but the data for all ten years needs to be available based on corporate policies.Please remember, the actual purpose of this article wasn't just to show you how to compute running totals on partitioned tables. The purpose was to demonstrate a methodology that could possibly be used to address problems with updates to partitioned tables. Can it be used in all cases, maybe not, but it is a tool that is available.Two other items. If you were to use the interim table method, you actually would only create a table with the necessary columns needed to complete the update. If the source table has 25 columns and is 2500 bytes wide, but you only need 6 columns and 75 bytes, that is all you would use. Same idea with the indexed view. You would only build the view using the columns you needed to perform the update.Wed, 28 Jan 2009 10:12:02 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxMakes sense now. I would still suggest that if the running totals requirement was the dominant form of querying on the table, I would rather just un-partition the whole table rather than create a non-partitioned duplicate of it (temporarily with the temp table or worse, permanently with the non-paritioned indexed view). Otherwise I would really try not to fufill any kind of real-time reporting requirement on it, only batch pre-generation of the aggregates.Wed, 28 Jan 2009 09:37:43 GMTYeshuaAgapaohttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]Adam Haines (1/27/2009)[/b][hr]Forgot to comment on the article :hehe:. I thought the article was very informative, easy to read/understand, and offered great alternative methods to solving the "running total" enigma. Nicely done.[/quote]Thank you, I appreciate your kind words regarding the article.Tue, 27 Jan 2009 21:32:39 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxForgot to comment on the article :hehe:. I thought the article was very informative, easy to read/understand, and offered great alternative methods to solving the "running total" enigma. Nicely done.Tue, 27 Jan 2009 21:05:41 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]Lynn Pettis (1/27/2009)[/b][hr][quote][b]Smendle (1/27/2009)[/b][hr]That can happen on multi-processor systems that SQL is allowed to use.[/quote]I am sure this is in response to the following:[quote]I received a private message with a question regarding my article that I can't answer, so I thought I'd put it here with the hopes that some of the gurus (Jeff, Gail, Grant, Gus, et al) out there may be able to help out.[quote]Why is the CPU time greater than the elapsed timeCPU time = 5063 ms, elapsed time = 3309 ms.[/quote] [/quote]Thinking about it, that actually makes sense and isn't something I'd have come up with on my own, thanks. Do any of the other gurus out there also agree?[/quote]The answer to this question is parallelism. The cpu time is greater because the query is spread across multiple processors, which allows you to do a lot more cpu processing in a shorter period of time. This is how CPU time becomes greater than elapsed time. If you set max dop to 1, you will not see the same behavior.Tue, 27 Jan 2009 20:57:02 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxAnd I must thank you in return, Jeff. If it hadn't been for you, and some of the other gurus on SSC, I never would have written this article. Your article was the basis of it, and your tutelage the catalyst.I also appreciate your having taken the time to read my initial draft of the article. Your comments and suggestions, I feel, helped the article as well.So, I have to give you a well done as well.Tue, 27 Jan 2009 20:03:38 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxI guess I should first say thank you for the wonderful acknowledgements. Thanks, Lynn.It's also a real pleasure to read an article with a logical progression and a whole lot of "Alice's Restaurant" code examples to play with especially when they're that readable. Very nicely done, Mr. Pettis. :)Tue, 27 Jan 2009 19:54:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]YeshuaAgapao (1/27/2009)[/b][hr]I don't get this. Both of the solutions create a non-partitioned instance of the partitioned table. One form is a temp table, and the other form is a materialized view (by the clustered index). This seems to me that it defeats the purpose of partitioning the table in the first place.[/quote]The purpose was to find a method for updating a partitioned table with running balances. The highly performant method discussed in Jeff Moden's article doesn't work on partitioned tables. So, how do you accomplish this task if you need to do it? The article shows you two methods for accomplishing this task.Behind all this, is a principle, how do you accomplish any task involving partitioned tables that fails, but works fine when done with a non-partitioned (or standard) table. The article wasn't discussing the pro's and con's of storing running totals in the database vs computing them on the client-side, or the pro's and con's of partitioned tables.I hope this explanation helps you with understanding the idea behind the article.Tue, 27 Jan 2009 18:41:41 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxI don't get this. Both of the solutions create a non-partitioned instance of the partitioned table. One form is a temp table, and the other form is a materialized view (by the clustered index). This seems to me that it defeats the purpose of partitioning the table in the first place.Tue, 27 Jan 2009 18:07:13 GMTYeshuaAgapaohttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxActually, Grant, I'm curious also, and with your experience with working with execution plans, your feedback on that side would be quite interesting.Tue, 27 Jan 2009 13:37:32 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]Lynn Pettis (1/27/2009)[/b][hr]Thank you, this makes me feel really good. I'll try to get the execution plans for and post them here. It may a few days, as I do have several things going on right now.[/quote]No rush. If I stumble across a spare hour or two I'll do it myself. I'm just curious.Tue, 27 Jan 2009 13:26:26 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]Grant Fritchey (1/27/2009)[/b][hr]Uh... Wow... Over-achieve much?I feel a bit stupid to be commenting on this. I don't have much experience with partitioning beyond reading up on the theory and a few discussions. If I can make some spare time, I'd like to see what the execution plans looked like between your final solution and Jeff's original, purely academic.Nice job.[/quote]Thank you, this makes me feel really good. I'll try to get the execution plans for you and post them here. It may a few days, as I do have several things going on right now.Tue, 27 Jan 2009 13:22:23 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxUh... Wow... Over-achieve much?I feel a bit stupid to be commenting on this. I don't have much experience with partitioning beyond reading up on the theory and a few discussions. If I can make some spare time, I'd like to see what the execution plans looked like between your final solution and Jeff's original, purely academic.Nice job.Tue, 27 Jan 2009 12:54:51 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]David Jackson (1/27/2009)[/b][hr][quote][b]Lynn Pettis (1/27/2009)[/b][hr][quote][b]Smendle (1/27/2009)[/b][hr]That can happen on multi-processor systems that SQL is allowed to use.[/quote]I am sure this is in response to the following:[quote]I received a private message with a question regarding my article that I can't answer, so I thought I'd put it here with the hopes that some of the gurus (Jeff, Gail, Grant, Gus, et al) out there may be able to help out.[quote]Why is the CPU time greater than the elapsed timeCPU time = 5063 ms, elapsed time = 3309 ms.[/quote] [/quote]Thinking about it, that actually makes sense and isn't something I'd have come up with on my own, thanks. Do any of the other gurus out there also agree?[/quote]I concur. What happens if you add MAXDOP = 1 to that particular query? FWIW, in a previous life MAXDOP was used as execution plans differed wildly on (single CPU, pre-hyper thread) Test kit than when they ran on the multi-CPU Live kit!Nice article btw ;)Dave Jackson[/quote]First, to remember my manners, thank you for the complement on the article.I'd have to go back into my article to figure out which one it was, and then try it again at home with MAXDOP set to 1. The other thing I'll need is to find the time to do it. Maybe I'll get a few minutes some time this week, but right now I'm a bit busy with another article as well as other life activities that are clamoring for my time.Tue, 27 Jan 2009 10:13:08 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]Lynn Pettis (1/27/2009)[/b][hr][quote][b]Smendle (1/27/2009)[/b][hr]That can happen on multi-processor systems that SQL is allowed to use.[/quote]I am sure this is in response to the following:[quote]I received a private message with a question regarding my article that I can't answer, so I thought I'd put it here with the hopes that some of the gurus (Jeff, Gail, Grant, Gus, et al) out there may be able to help out.[quote]Why is the CPU time greater than the elapsed timeCPU time = 5063 ms, elapsed time = 3309 ms.[/quote] [/quote]Thinking about it, that actually makes sense and isn't something I'd have come up with on my own, thanks. Do any of the other gurus out there also agree?[/quote]I concur. What happens if you add MAXDOP = 1 to that particular query? FWIW, in a previous life MAXDOP was used as execution plans differed wildly on (single CPU, pre-hyper thread) Test kit than when they ran on the multi-CPU Live kit!Nice article btw ;)Dave JacksonTue, 27 Jan 2009 10:00:22 GMTDavid Jacksonhttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]Smendle (1/27/2009)[/b][hr]That can happen on multi-processor systems that SQL is allowed to use.[/quote]I am sure this is in response to the following:[quote]I received a private message with a question regarding my article that I can't answer, so I thought I'd put it here with the hopes that some of the gurus (Jeff, Gail, Grant, Gus, et al) out there may be able to help out.[quote]Why is the CPU time greater than the elapsed timeCPU time = 5063 ms, elapsed time = 3309 ms.[/quote] [/quote]Thinking about it, that actually makes sense and isn't something I'd have come up with on my own, thanks. Do any of the other gurus out there also agree?Tue, 27 Jan 2009 09:26:26 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxApologies, I realise that my previous post represented something of a Utopian ideal :-) With that said however, I do think that whilst making do and mending is *good*, striving for a simpler way is not to be dismissed. Yes, perhaps Microsoft will eventually get around to properly implementing such functionality.Whilst writing this, I can see that a middle tier person just rode in to town. 6 lines of code vs. potentially 1,000,000 rows of data over the network.When does the next craft to Utopia depart?Tue, 27 Jan 2009 09:19:59 GMTJohn Walker-229141http://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]noeld (1/27/2009)[/b][hr]I liked your article ( a lot ) and it seems that you put a lot of effort into it.[/quote]And I'm forgetting my manners. Thank you, it makes me feel good making the effort definitely worthwhile.Tue, 27 Jan 2009 09:18:56 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxThat can happen on multi-processor systems that SQL is allowed to use.Tue, 27 Jan 2009 09:14:34 GMTSmendlehttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxI received a private message with a question regarding my article that I can't answer, so I thought I'd put it here with the hopes that some of the gurus (Jeff, Gail, Grant, Gus, et al) out there may be able to help out.[quote]Why is the CPU time greater than the elapsed timeCPU time = 5063 ms, elapsed time = 3309 ms.[/quote]Tue, 27 Jan 2009 09:01:40 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]noeld (1/27/2009)[/b][hr]I liked your article ( a lot ) and it seems that you put a lot of effort into it.My view of running totals is simple. It is a CLIENT-SIDE responsibility.Although it may provide you with an alternative to deliver to the developers what they may want out of the DB IMHO looping through an ordered result set in the client is by far more scalable and "faster" end-to-end from the User point of view.Just my $0.02 [/quote]I'll agree, which is why I had that in the disclaimer at the beginning of the article. What I was really looking at was a process to solve a problem. As I have found many times, prinicples are more important than details. The running total problem with partitioned tables offered a problem and hopefully this article provides a principle that can be applied to similiar problems that may not be a client-side responsibility.An accounting professor once told me he rather teach 10 principles rather than 10 detailed tasks. With 10 principles you complete 100 tasks.Tue, 27 Jan 2009 08:59:21 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxI liked your article ( a lot ) and it seems that you put a lot of effort into it.My view of running totals is simple. It is a CLIENT-SIDE responsibility.Although it may provide you with an alternative to deliver to the developers what they may want out of the DB IMHO looping through an ordered result set in the client is by far more scalable and "faster" end-to-end from the User point of view.Just my $0.02 Tue, 27 Jan 2009 08:37:57 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]Smendle (1/27/2009)[/b][hr][quote]I wish I had a true single processor system (no hyper threading or multiple cores) to have tested this on to see how it worked. Would I have thought that this worked without MAXDOP 1 in that environment? If it did, what would my reaction have been when I moved it to multi-processor, multi-core environment and it suddenly failed (remember, I did a final test in such an environment)?[/quote]Oh but you do! Go into your BIOS and turn that stuff off!:hehe:[/quote]Maybe, but I'm a software geek, not a hardware geek. I'm not exactly sure how to go about doing that, and I'd be worried that I'd break something (and I'm not joking on that).Tue, 27 Jan 2009 07:15:44 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote]I wish I had a true single processor system (no hyper threading or multiple cores) to have tested this on to see how it worked. Would I have thought that this worked without MAXDOP 1 in that environment? If it did, what would my reaction have been when I moved it to multi-processor, multi-core environment and it suddenly failed (remember, I did a final test in such an environment)?[/quote]Oh but you do! Go into your BIOS and turn that stuff off!:hehe:Tue, 27 Jan 2009 07:10:42 GMTSmendlehttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]peter (1/27/2009)[/b][hr]Not having access to a developer or Enterprise version of SQL Server I find this topic hard to go into myself. Some of the SQL features used are only available on those versions for those wondering why I bring it up. Still I did quickly read trough the essential parts that make up the solution and the path followed to get there and got a bit of a bad feeling after reading it.Searching for working methods is one thing, but what bugs me in this case is that the final output depends on query hints like OPTION (MAXDOP 1) that technically should not affect the output at all. The solution is relying on SQL Server implementation details that are unspecified by microsoft and are likely to change over time.Even when implementation details do not change, maintainability is at risk because the technique breaks with the very useful concept of SQL itself. You ask a question and specify [b]what information[/b] you want as output, [b]in what order[/b]. It is then up to the database to deliver according to your specifications as best as it can, isolating you from the details on [b]how[/b] it does it.Imagine using the described technique and 4 years from now you move to a 4 processor, 12 core system and solid state disks. There turns out to be unexpected low performance and an expert is hired to see what is wrong. First thing that would get the boot would be the OPTION (MAXDOP 1). It would speed the result significantly and since such a hint SHOULD NEVER affect output it is unlikely to be noticed right away.You see where I am going with this....it feels like playing dice with your data![/quote]I understand your concern, however, part of it can be solved by one simple thing and that is documentation. If this technique is used in a production system, the use of the OPTION (MAXDOP 1) should be well commented so that someone else coming along later would understand why it was being used and would not remove it, thereby breaking the process.I wish I had a true single processor system (no hyper threading or multiple cores) to have tested this on to see how it worked. Would I have thought that this worked without MAXDOP 1 in that environment? If it did, what would my reaction have been when I moved it to multi-processor, multi-core environment and it suddenly failed (remember, I did a final test in such an environment)?Maybe someone out there has a true single processor system and access to SQL Server 2005 Developer Edition and they will take the code from this article and run these tests and let us know the results of that testing.Tue, 27 Jan 2009 06:49:00 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspx[quote][b]John Walker (1/27/2009)[/b][hr]I think Jeff hinted that he was going to eventually get to the analytics solution and far be it from me to steal his thunder, but I couldn't help wondering if either I have misunderstood the scale of the problem or the solutions displayed thus far have been grossly convoluted. From my understanding of the problem at its simplest, we would like to compute a running total (leaving any notion of partitioning aside for the moment). Surely all that is needed is something like this: select AccountId, Date, Amount, sum(Amount) over (partition by AccountId, order by Date) as "Running_Balance" from JBMTest order by AccountId, Date Now, partitioning. What has that got to do with anything? Again, from our simple perspective we don't care. Let our optimizer handle such details. As the query stands, we're looking to cover the entire table anyway. Given that our partitions are based on date, if we restrict to one year, we touch only one partition - but that is not something we need to explicitly state in a hint or some such construct, surely.[/quote]Unfortunately, at this time, the window functions in SQL Server are limited and won't give you the answers that you need to answer the running total question. The row_number() function can be used in SQL Server 2005/2008 to handle the ranking problem, but that still leaves running balanaces. Until Microsoft fully implements the windowing functions in SQL Server, and you are looking to implement proformant code to meet this need, you will need to look at methods like this that utilize the database engine to its fullest.In addition, this is but an example that happens to use date for the partitioning. What if the partioning were done based on account ranges, for example, because the application design merited such a design. Please remember, the purpose of the article was to demonstrate a technique that could be used to help solve a problem.Tue, 27 Jan 2009 06:39:13 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxNot having access to a developer or Enterprise version of SQL Server I find this topic hard to go into myself. Some of the SQL features used are only available on those versions for those wondering why I bring it up. Still I did quickly read trough the essential parts that make up the solution and the path followed to get there and got a bit of a bad feeling after reading it.Searching for working methods is one thing, but what bugs me in this case is that the final output depends on query hints like OPTION (MAXDOP 1) that technically should not affect the output at all. The solution is relying on SQL Server implementation details that are unspecified by microsoft and are likely to change over time.Even when implementation details do not change, maintainability is at risk because the technique breaks with the very useful concept of SQL itself. You ask a question and specify [b]what information[/b] you want as output, [b]in what order[/b]. It is then up to the database to deliver according to your specifications as best as it can, isolating you from the details on [b]how[/b] it does it.Imagine using the described technique and 4 years from now you move to a 4 processor, 12 core system and solid state disks. There turns out to be unexpected low performance and an expert is hired to see what is wrong. First thing that would get the boot would be the OPTION (MAXDOP 1). It would speed the result significantly and since such a hint SHOULD NEVER affect output it is unlikely to be noticed right away.You see where I am going with this....it feels like playing dice with your data!Tue, 27 Jan 2009 05:58:09 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic643896-1323-1.aspxI think Jeff hinted that he was going to eventually get to the analytics solution and far be it from me to steal his thunder, but I couldn't help wondering if either I have misunderstood the scale of the problem or the solutions displayed thus far have been grossly convoluted. From my understanding of the problem at its simplest, we would like to compute a running total (leaving any notion of partitioning aside for the moment). Surely all that is needed is something like this: select AccountId, Date, Amount, sum(Amount) over (partition by AccountId, order by Date) as "Running_Balance" from JBMTest order by AccountId, Date Now, partitioning. What has that got to do with anything? Again, from our simple perspective we don't care. Let our optimizer handle such details. As the query stands, we're looking to cover the entire table anyway. Given that our partitions are based on date, if we restrict to one year, we touch only one partition - but that is not something we need to explicitly state in a hint or some such construct, surely.Tue, 27 Jan 2009 03:19:55 GMTJohn Walker-229141