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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » The OVER Clause enhancements in SQL Server...

35 posts, Page 1 of 4

1 2 3 4 »»»

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

Rate Topic

Display Mode

Topic Options

Author

Message

WayneS

Posted Wednesday, August 24, 2011 9:18 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,367, Visits: 8,227

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

madonl

Posted Wednesday, August 24, 2011 9:22 PM

Forum 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

augustine.damba 67604

Posted Wednesday, August 24, 2011 10:50 PM

Forum 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

madonl

Posted Wednesday, August 24, 2011 11:46 PM

Forum 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

Paul White

Posted Thursday, August 25, 2011 5:40 AM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

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

WayneS

Posted Thursday, August 25, 2011 6:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,367, Visits: 8,227

augustine.damba 67604 (8/24/2011)

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!

Post #1165288

Jack Corbett

Posted Thursday, August 25, 2011 6:50 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

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

WayneS

Posted Thursday, August 25, 2011 7:01 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,367, Visits: 8,227

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!

Post #1165310

jcrawf02

Posted Thursday, August 25, 2011 7:42 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:36 PM
Points: 2,549, Visits: 18,880

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

HowardW

Posted Thursday, August 25, 2011 8:17 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:55 AM
Points: 1,034, Visits: 7,660

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 »

35 posts, Page 1 of 4

1 2 3 4 »»»

Permissions