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

Question

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

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Revenant SSC-Forever Points: 42467 More actions · Answer 1

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 SSC Guru Points: 281334 More actions · Answer 2

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[/url]
Learn Extended Events

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 3

Great article. Thank you.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

UMG Developer SSChampion Points: 13482 More actions · Answer 5

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.)

WayneS SSC Guru Points: 95409 More actions · Answer 6

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
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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ken SSCoach Points: 17191 More actions · Answer 7

Wayne - Any chance those edited sections will appear in a future article?

WayneS SSC Guru Points: 95409 More actions · Answer 8

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
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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

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 on googles mail service

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 11

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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

WayneS SSC Guru Points: 95409 More actions · Answer 12

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[/url]).

(Or, you could click the little blue "Blog" button below any of my posts here on SSC. 😉 )

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95409 More actions · Answer 13

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
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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 14

Awesome article! Easy to read and very informative.

I especially liked that you provided simple easy-to-understand examples. 🙂

WayneS SSC Guru Points: 95409 More actions · Answer 15

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
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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2