Post reply

The OVER Clause enhancements in SQL Server code named “Denali”, CTP3

  • August 24, 2011 at 9:18 pm

    #390269

    Comments posted to this topic are about the item The OVER Clause enhancements in SQL Server code named “Denali”, CTP3

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 24, 2011 at 9:22 pm

    #1372927

    Will the enhanced Over clause also support *user defined* aggregate functions?

  • August 24, 2011 at 10:50 pm

    #1372936

    Hi,

    Could you please verify that the Select statement and the Over() is correct for

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

  • August 24, 2011 at 11:46 pm

    #1372944

    I should have googled before posting my question, the answer is already out there. Denali doesn't support UDAs with the Over clause. Oh well.

  • August 25, 2011 at 5:40 am

    #1373051

    madonl (8/24/2011)

    I should have googled before posting my question, the answer is already out there. Denali doesn't support UDAs with the Over clause. Oh well.

    You can vote for Bob's connect item here:

    http://connect.microsoft.com/SQLServer/feedback/details/681155/windowing-enhancements-not-available-on-sqlclr-aggregates

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • August 25, 2011 at 6:36 am

    #1373092

    augustine.damba 67604 (8/24/2011)

    Hi,

    Could you please verify that the Select statement and the Over() is correct for

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

    Take a closer look... the parameters are not the same. The SumByRows is using "ROWS UNBOUNDED PRECEDING", and SumByRange is using "RANGE UNBOUNDED PRECEDING"

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 25, 2011 at 6:50 am

    #1373101

    Good article Wayne. Clear and concise examples. I'm looking forward to the running totals ability.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • August 25, 2011 at 7:01 am

    #1373111

    Jack Corbett (8/25/2011)

    Good article Wayne. Clear and concise examples.

    Thanks Jack.

    I'm looking forward to the running totals ability.

    Then check out my blog post[/url] on it.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 25, 2011 at 7:42 am

    #1373152

    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?

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • August 25, 2011 at 8:17 am

    #1373176

    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

  • August 25, 2011 at 8:23 am

    #1373186

    jcrawf02 (8/25/2011)

    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?

    Absolutely Jon - that is exactly what I was trying to convey.

    Glad you liked it!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 25, 2011 at 8:53 am

    #1373211

    WayneS (8/25/2011)

    Jack Corbett (8/25/2011)

    Good article Wayne. Clear and concise examples.

    Thanks Jack.

    I'm looking forward to the running totals ability.

    Then check out my blog post[/url] on it.

    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.

  • August 25, 2011 at 9:23 am

    #1373249

    HowardW (8/25/2011)

    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

    Thanks Howard.

    I also am hoping that it's just debug/beta code causing the issues.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 25, 2011 at 9:25 am

    #1373251

    Carla Wilson-484785 (8/25/2011)

    WayneS (8/25/2011)

    Jack Corbett (8/25/2011)

    Good article Wayne. Clear and concise examples.

    Thanks Jack.

    I'm looking forward to the running totals ability.

    Then check out my blog post[/url] on it.

    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.

    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.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 25, 2011 at 10:09 am

    #1373308

    Nice post. Definately looking forward to using these new functions. I can see it making our trending reports easier to build for sure.

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply