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 2:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
Jeff Moden (8/25/2011)
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.


Thanks Jeff,

The editor already had me remove some significant sections to keep it simple for the join-challenged amongst us... wanted to keep this article about just the enhancements to the over clause, and not really go beyond that.

Plus, it gave me a way to promote the blog when the running totals came up in the discussion thread.


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 #1165760
Posted Thursday, August 25, 2011 3:11 PM


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: 2 days ago @ 11:29 AM
Points: 3,354, Visits: 2,001
Wayne - Any chance those edited sections will appear in a future article?
Post #1165770
Posted Thursday, August 25, 2011 3:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
Kenneth Wymore (8/25/2011)
Wayne - Any chance those edited sections will appear in a future article?

Always a chance, and it would give me an opportunity to expand upon it also.


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 #1165777
Posted Thursday, August 25, 2011 3:41 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
WayneS (8/25/2011)
Jeff Moden (8/25/2011)
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.


Thanks Jeff,

The editor already had me remove some significant sections to keep it simple for the join-challenged amongst us... wanted to keep this article about just the enhancements to the over clause, and not really go beyond that.

Plus, it gave me a way to promote the blog when the running totals came up in the discussion thread.


BWAA-HAAA!!! Editors... what do they know? (Hi Steve!)

Understood on all fronts. Thanks for the feedback, Wayne. And, I'll say it again, well done on the article and the blog both!


--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 #1165778
Posted Thursday, August 25, 2011 4:01 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 4,400, Visits: 6,259
Wayne, can you please email me a link to your blog with the perf testing of the Denali OVER enhancements? I did a web search but couldn't find it. TIA!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1165785
Posted Thursday, August 25, 2011 4:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
Wayne,

Excellent examples and a solid walkthrough, thank you.

Btw, for your question about real-use of range, you'd have to wrap the results in a distinct.

I'm thinking of log-entries. If you want by day-running totals, you'd do a by range and then distinct the results on the summed columns and the leading identifiers. This way if 2 ppl worked on a project 1 day and 10 people the next you could still have 2 rows, one identifying each day after the distinct with the correct totals.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1165800
Posted Thursday, August 25, 2011 6:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
TheSQLGuru (8/25/2011)
Wayne, can you please email me a link to your blog with the perf testing of the Denali OVER enhancements? I did a web search but couldn't find it. TIA!


Hi Kevin,

I guess you missed it up above, in my reply to Jack. Anyway, it's http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/ (it was disguised as my blog post).
(Or, you could click the little blue "Blog" button below any of my posts here on SSC. )


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 #1165819
Posted Thursday, August 25, 2011 6:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
Kenneth, Revenant, Jason, Andre, UMG, Craig:

Thanks for your positive feedback, and I'm very glad that you liked the article.


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 #1165820
Posted Friday, August 26, 2011 8:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 1:09 PM
Points: 478, Visits: 1,417
Awesome article! Easy to read and very informative.
I especially liked that you provided simple easy-to-understand examples.
Post #1166106
Posted Friday, August 26, 2011 9:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
Goldie Lesser (8/26/2011)
Awesome article! Easy to read and very informative.
I especially liked that you provided simple easy-to-understand examples.


Thanks Goldie. I try to provide easy to understand examples to aid people in seeing how something they currently have can be easily adapted.


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 #1166172
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse