Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    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...
Add to briefcase
35 posts, Page 2 of 4
««1234»»»

The OVER Clause enhancements in SQL Server code named “Denali”, CTP3 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
WayneS
Posted Thursday, August 25, 2011 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370, Visits: 8,235
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
Carla Wilson-484785
Posted Thursday, August 25, 2011 8:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:15 AM
Points: 1,352, Visits: 1,738
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
WayneS
Posted Thursday, August 25, 2011 9:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370, Visits: 8,235
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
WayneS
Posted Thursday, August 25, 2011 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370, Visits: 8,235
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
KWymore
Posted Thursday, August 25, 2011 10:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 2,575, Visits: 1,533
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
Revenant
Posted Thursday, August 25, 2011 10:22 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: Today @ 1:00 PM
Points: 3,459, Visits: 2,535
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
SQLRNNR
Posted Thursday, August 25, 2011 10:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332
Good article Wayne.


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

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
codebyo
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, May 16, 2013 5:30 AM
Points: 861, Visits: 1,436
Great article. Thank you.

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1165604
Jeff Moden
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 @ 7:51 PM
Points: 32,910, Visits: 26,800
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1165736
UMG Developer
Posted Thursday, August 25, 2011 2:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148
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
35 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.