Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

The OVER Clause enhancements in SQL Server code named “Denali”, CTP3 Expand / Collapse
Author
Message
Posted Wednesday, August 24, 2011 9:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 AM
Points: 6,594, Visits: 8,878
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1165093
Posted Wednesday, August 24, 2011 9:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 24, 2011 11:38 PM
Points: 2, Visits: 1
Will the enhanced Over clause also support *user defined* aggregate functions?
Post #1165095
Posted Wednesday, August 24, 2011 10:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 24, 2013 8:49 PM
Points: 4, Visits: 11
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.
Post #1165105
Posted Wednesday, August 24, 2011 11:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 24, 2011 11:38 PM
Points: 2, Visits: 1
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.
Post #1165115
Posted Thursday, August 25, 2011 5:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1165244
Posted Thursday, August 25, 2011 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 AM
Points: 6,594, Visits: 8,878
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1165288
Posted Thursday, August 25, 2011 6:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:28 PM
Points: 11,252, Visits: 13,015
Good article Wayne. Clear and concise examples. I'm looking forward to the running totals ability.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1165298
Posted Thursday, August 25, 2011 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 AM
Points: 6,594, Visits: 8,878
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 on it.


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1165310
Posted Thursday, August 25, 2011 7:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:17 AM
Points: 2,669, Visits: 19,237
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #1165353
Posted Thursday, August 25, 2011 8:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 1,237, Visits: 9,802
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
Post #1165379
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse