InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 14:45:56 GMT20http://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxGianluca, I'm glad that you liked both the article and the blog. Thanks!Tue, 30 Aug 2011 19:09:36 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxNice article, Wayne!And I also liked your post on the performance comparison on your blog.Cool stuff. Thank you.Tue, 30 Aug 2011 05:45:58 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]mishaluba (8/27/2011)[/b][hr]I had no idea you could use PARTITION BY with things like MIN, MAX, COUNT, etc. :blush:Thank you for the article![/quote]You're welcome... and I'm glad you've learned from it!Sun, 28 Aug 2011 08:01:54 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxI had no idea you could use PARTITION BY with things like MIN, MAX, COUNT, etc. :blush:Thank you for the article!Sat, 27 Aug 2011 21:46:33 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]Evil Kraig F (8/25/2011)[/b][hr]Wayne,Excellent examples and a solid walkthrough, thank you.Btw, for your question about real-use of range, you'd have to wrap the results in a distinct.I'm thinking of log-entries. If you want by day-running totals, you'd do a by range and then distinct the results on the summed columns and the leading identifiers. This way if 2 ppl worked on a project 1 day and 10 people the next you could still have 2 rows, one identifying each day after the distinct with the correct totals.[/quote]Ah! Great example - thanks!Fri, 26 Aug 2011 09:31:39 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]Goldie Lesser (8/26/2011)[/b][hr]Awesome article! Easy to read and very informative. I especially liked that you provided simple easy-to-understand examples. :-)[/quote]Thanks Goldie. I try to provide easy to understand examples to aid people in seeing how something they currently have can be easily adapted.Fri, 26 Aug 2011 09:27:21 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxAwesome article! Easy to read and very informative. I especially liked that you provided simple easy-to-understand examples. :-)Fri, 26 Aug 2011 08:35:07 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxKenneth, Revenant, Jason, Andre, UMG, Craig:Thanks for your positive feedback, and I'm very glad that you liked the article.Thu, 25 Aug 2011 18:53:19 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]TheSQLGuru (8/25/2011)[/b][hr]Wayne, can you please email me a link to your blog with the perf testing of the Denali OVER enhancements? I did a web search but couldn't find it. TIA![/quote]Hi Kevin,I guess you missed it up above, in my reply to Jack. Anyway, it's [url]http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/[/url] (it was disguised as [url=http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/][u]my blog post[/u][/url]).(Or, you could click the little blue "Blog" button below any of my posts here on SSC. ;-) )Thu, 25 Aug 2011 18:45:29 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxWayne,Excellent examples and a solid walkthrough, thank you.Btw, for your question about real-use of range, you'd have to wrap the results in a distinct.I'm thinking of log-entries. If you want by day-running totals, you'd do a by range and then distinct the results on the summed columns and the leading identifiers. This way if 2 ppl worked on a project 1 day and 10 people the next you could still have 2 rows, one identifying each day after the distinct with the correct totals.Thu, 25 Aug 2011 16:33:39 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxWayne, can you please email me a link to your blog with the perf testing of the Denali OVER enhancements? I did a web search but couldn't find it. TIA!Thu, 25 Aug 2011 16:01:07 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]WayneS (8/25/2011)[/b][hr][quote][b]Jeff Moden (8/25/2011)[/b][hr]Great article, Wayne, although I'm a bit surprised you didn't publish the million row performance findings (like you did on your blog) which shows just how big a performance and "reads" problem you can run into with this new functionality.Still, the new functionality will beat a cursor.[/quote]Thanks Jeff,The editor already had me remove some significant sections to keep it simple for the join-challenged amongst us... wanted to keep this article about just the enhancements to the over clause, and not really go beyond that.Plus, it gave me a way to promote the blog when the running totals came up in the discussion thread. :-D[/quote]BWAA-HAAA!!! Editors... what do they know? :-P (Hi Steve!)Understood on all fronts. Thanks for the feedback, Wayne. And, I'll say it again, well done on the article and the blog both! :-)Thu, 25 Aug 2011 15:41:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]Kenneth Wymore (8/25/2011)[/b][hr]Wayne - Any chance those edited sections will appear in a future article?[/quote]Always a chance, and it would give me an opportunity to expand upon it also.Thu, 25 Aug 2011 15:40:54 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxWayne - Any chance those edited sections will appear in a future article?Thu, 25 Aug 2011 15:11:32 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]Jeff Moden (8/25/2011)[/b][hr]Great article, Wayne, although I'm a bit surprised you didn't publish the million row performance findings (like you did on your blog) which shows just how big a performance and "reads" problem you can run into with this new functionality.Still, the new functionality will beat a cursor.[/quote]Thanks Jeff,The editor already had me remove some significant sections to keep it simple for the join-challenged amongst us... wanted to keep this article about just the enhancements to the over clause, and not really go beyond that.Plus, it gave me a way to promote the blog when the running totals came up in the discussion thread. :-DThu, 25 Aug 2011 14:48:23 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxThanks for the article, I have been looking forward to this functionality. Thanks also for the blog post about the performance. (It would have been good if that was included in the article.)Thu, 25 Aug 2011 14:18:25 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxGreat article, Wayne, although I'm a bit surprised you didn't publish the million row performance findings (like you did on your blog) which shows just how big a performance and "reads" problem you can run into with this new functionality.Still, the new functionality will beat a cursor.Thu, 25 Aug 2011 14:17:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxGreat article. Thank you.Best regards,Thu, 25 Aug 2011 11:21:32 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxGood article Wayne.Thu, 25 Aug 2011 10:25:35 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxThis is an interesting post.I would just correct one small detail: "'Denali' was recently released as beta #3 (otherwise known as Community Technology Preview 3 (CTP3))"Microsoft CTPs are not betas - they often demo features that are "on the bubble" and may or may not be included.Betas are "feature complete" and undergo a complete cycle of testing before they are released. (Which does not mean they are bug free - it means that someone decided that it is OK if this beta has a particular "known issue.")Thu, 25 Aug 2011 10:22:51 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxNice post. Definately looking forward to using these new functions. I can see it making our trending reports easier to build for sure.Thu, 25 Aug 2011 10:09:03 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]Carla Wilson-484785 (8/25/2011)[/b][hr][quote][b]WayneS (8/25/2011)[/b][hr][quote][b]Jack Corbett (8/25/2011)[/b][hr]Good article Wayne. Clear and concise examples.[/quote]Thanks Jack.[quote]I'm looking forward to the running totals ability.[/quote]Then check out [url=http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/][u]my blog post[/u][/url] on it.[/quote]Cool! Thanks for the excellent explanation of the new features of OVER(). I'm trying to think of a practical use for RANGE, based on the way it works differently.I WAS really excited about the new OVER () for running aggregations, until I read your blog about testing the performance! Great test, BTW. Keep us posted.[/quote]Thanks Carla.Let us know if you can come up with a practical use for RANGE - I couldn't.I'll post an update on the blog when it hits RTM and I've retested those running totals.Thu, 25 Aug 2011 09:25:04 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]HowardW (8/25/2011)[/b][hr]Nice blog post Wayne - shame the performance doesn't look great at the moment for a common use-case. Hope they sort it out before RTM - I don't recall the Oracle version having any huge performance hit on the windowing functions, so not sure there's a major architectural reason it has to have so many reads[/quote]Thanks Howard.I also am hoping that it's just debug/beta code causing the issues.Thu, 25 Aug 2011 09:23:22 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]WayneS (8/25/2011)[/b][hr][quote][b]Jack Corbett (8/25/2011)[/b][hr]Good article Wayne. Clear and concise examples.[/quote]Thanks Jack.[quote]I'm looking forward to the running totals ability.[/quote]Then check out [url=http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/][u]my blog post[/u][/url] on it.[/quote]Cool! Thanks for the excellent explanation of the new features of OVER(). I'm trying to think of a practical use for RANGE, based on the way it works differently.I WAS really excited about the new OVER () for running aggregations, until I read your blog about testing the performance! Great test, BTW. Keep us posted.Thu, 25 Aug 2011 08:53:16 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]jcrawf02 (8/25/2011)[/b][hr]Nice job Wayne! So, assuming I understand this correctly, I can use the "Sliding Aggregations" to show a six-month trailing trend, or something similar?[/quote]Absolutely Jon - that is exactly what I was trying to convey.Glad you liked it!Thu, 25 Aug 2011 08:23:26 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxNice blog post Wayne - shame the performance doesn't look great at the moment for a common use-case. Hope they sort it out before RTM - I don't recall the Oracle version having any huge performance hit on the windowing functions, so not sure there's a major architectural reason it has to have so many readsThu, 25 Aug 2011 08:17:48 GMTHowardWhttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxNice job Wayne! So, assuming I understand this correctly, I can use the "Sliding Aggregations" to show a six-month trailing trend, or something similar?Thu, 25 Aug 2011 07:42:14 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]Jack Corbett (8/25/2011)[/b][hr]Good article Wayne. Clear and concise examples.[/quote]Thanks Jack.[quote]I'm looking forward to the running totals ability.[/quote]Then check out [url=http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/][u]my blog post[/u][/url] on it.Thu, 25 Aug 2011 07:01:55 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxGood article Wayne. Clear and concise examples. I'm looking forward to the running totals ability.Thu, 25 Aug 2011 06:50:27 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]augustine.damba 67604 (8/24/2011)[/b][hr]Hi,Could you please verify that the Select statement and the Over() is correct forSumByRows and SumByRange?The Over() function is using same parameters but the results are different.I have not yet installed Denali so I cannot test this on my PC.[/quote]Take a closer look... the parameters are not the same. The SumByRows is using "[b][u]ROWS[/u][/b] UNBOUNDED PRECEDING", and SumByRange is using "[b][u]RANGE[/u][/b] UNBOUNDED PRECEDING"Thu, 25 Aug 2011 06:36:16 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspx[quote][b]madonl (8/24/2011)[/b][hr]I should have googled before posting my question, the answer is already out [url=http://www.sqlskills.com/BLOGS/BOBB/post/Hold-it-right-there-youre-a-user-defined-aggregate.aspx]there[/url]. Denali doesn't support UDAs with the Over clause. Oh well.[/quote]You can vote for Bob's connect item here:[url]http://connect.microsoft.com/SQLServer/feedback/details/681155/windowing-enhancements-not-available-on-sqlclr-aggregates[/url]Thu, 25 Aug 2011 05:40:57 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxI should have googled before posting my question, the answer is already out [url=http://www.sqlskills.com/BLOGS/BOBB/post/Hold-it-right-there-youre-a-user-defined-aggregate.aspx]there[/url]. Denali doesn't support UDAs with the Over clause. Oh well.Wed, 24 Aug 2011 23:46:27 GMTmadonlhttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxHi,Could you please verify that the Select statement and the Over() is correct forSumByRows and SumByRange?The Over() function is using same parameters but the results are different.I have not yet installed Denali so I cannot test this on my PC.Wed, 24 Aug 2011 22:50:58 GMTaugustine.damba 67604http://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxWill the enhanced Over clause also support *user defined* aggregate functions?Wed, 24 Aug 2011 21:22:57 GMTmadonlhttp://www.sqlservercentral.com/Forums/Topic1165093-1273-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/T-SQL/75466/">The OVER Clause enhancements in SQL Server code named “Denali”, CTP3</A>[/B]Wed, 24 Aug 2011 21:18:58 GMTWayneS