SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21126 Visits: 10652
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, 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

Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2276 Visits: 1951
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 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.
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21126 Visits: 10652
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, 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

WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21126 Visits: 10652
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 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, 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

Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6547 Visits: 2396
Nice post. Definately looking forward to using these new functions. I can see it making our trending reports easier to build for sure.
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12347 Visits: 5010
This 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.")
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65265 Visits: 18570
Good article Wayne.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Andre Guerreiro
Andre Guerreiro
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2231 Visits: 1515
Great article. Thank you.

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210043 Visits: 41973
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.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
UMG Developer
UMG Developer
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3966 Visits: 2204
Thanks 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.)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search