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

  • 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

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

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

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

  • 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

  • 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

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

  • 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

  • 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."

  • 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

  • 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

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

  • 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

  • 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

  • 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