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 Thursday, August 25, 2011 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:07 AM
Points: 6,594, Visits: 8,881
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
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 #1165389
Posted Thursday, August 25, 2011 8:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:34 AM
Points: 1,566, Visits: 1,851
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.
Post #1165420
Posted Thursday, August 25, 2011 9:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:07 AM
Points: 6,594, Visits: 8,881
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
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 #1165463
Posted Thursday, August 25, 2011 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:07 AM
Points: 6,594, Visits: 8,881
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
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 #1165466
Posted Thursday, August 25, 2011 10:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:23 AM
Points: 3,299, Visits: 1,978
Nice post. Definately looking forward to using these new functions. I can see it making our trending reports easier to build for sure.
Post #1165530
Posted Thursday, August 25, 2011 10:22 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 AM
Points: 4,425, Visits: 3,417
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.")
Post #1165543
Posted Thursday, August 25, 2011 10:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
Good article Wayne.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1165546
Posted Thursday, August 25, 2011 11:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:59 AM
Points: 897, Visits: 1,488
Great article. Thank you.

Best regards,



Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1165604
Posted Thursday, August 25, 2011 2:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1165736
Posted Thursday, August 25, 2011 2:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.)
Post #1165738
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse