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 12»»

SQL Server's implementation of Window Functions Expand / Collapse
Author
Message
Posted Saturday, September 25, 2010 11:31 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 5:03 AM
Points: 579, Visits: 2,520
Comments posted to this topic are about the item SQL Server's implementation of Window Functions


Best wishes,

Phil Factor
Simple Talk
Post #993279
Posted Sunday, September 26, 2010 10:38 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 645, Visits: 1,651
PostgreSQL has been on our radar for a while. While focused on Oracle at the high-end, the folks in Redmond shouldn't forgot about PostgreSQL on the free end.
Post #993381
Posted Sunday, September 26, 2010 11:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 6,594, Visits: 8,879
Itzik Ben-Gan has several connect items in an effort to get full support for the windowing functions added to SQL Server. If you're interested, see the following links:
(If you agree, vote Yes. It might just make a difference as to what features get into the next version of SQL.)

Ordering for aggregates (used to allow subsequent framing options)

Framing (ROWS and RANGE window sub-clauses)

DISTINCT clause for aggregate functions

LAG and LEAD offset functions

PERCENT_RANK and CUME_DIST Distribution Functions

FIRST_VALUE, LAST_VALUE offset functions

Reuse of window definition using the WINDOW clause

QUALIFY filtering clause


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 #993385
Posted Sunday, September 26, 2010 1:02 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 5:03 AM
Points: 579, Visits: 2,520
Itzik has written a very fine blog on the subject of the missing functions Window Functions (OVER Clause) – Help Make a Difference. Also worth a look is PostgreSQL's documentation for Window Functions just so you can see what you're missingWindow Functions and Running totals and sums using PostgreSQL 8.4 Windowing functions . This is a cause that is well-worth supporting, so please vote on Connect for the implementation of all these functions. The most important of all is the first one 'Ordering for Aggregates' as this would allow a simple solution to a whole lot of 'running total' SQL problems.


Best wishes,

Phil Factor
Simple Talk
Post #993390
Posted Sunday, September 26, 2010 2:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 6,594, Visits: 8,879
Phil Factor (9/26/2010)
Itzik has written a very fine blog on the subject of the missing functions Window Functions (OVER Clause) – Help Make a Difference. Also worth a look is PostgreSQL's documentation for Window Functions just so you can see what you're missingWindow Functions and Running totals and sums using PostgreSQL 8.4 Windowing functions . This is a cause that is well-worth supporting, so please vote on Connect for the implementation of all these functions. The most important of all is the first one 'Ordering for Aggregates' as this would allow a simple solution to a whole lot of 'running total' SQL problems.


Thanks for the PostgreSQL links - they are indeed interesting and illuminating.
I didn't include a link for the blog since you need to have a paid membership to view it.

I agree with the most important missing function. I would say that IMHO, the next two are the ROWS/RANGE window sub-clauses, and the WINDOW clause. And if I understand all of these correctly, wouldn't you need the ROWS/RANGE into order to do the 'running total'?


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 #993394
Posted Sunday, September 26, 2010 3:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Phil,

Thanks for the editorial and bringing this back to peoples attention.

I like how a number of the connect items have comments from Microsoft in 2007 saying that they couldn't get it in 2008 but that it was a priority for the next version. Well 2008 R2 came and they still haven't gotten them in. We can only hope that they will make it into v11...
Post #993396
Posted Monday, September 27, 2010 1:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 8:16 AM
Points: 295, Visits: 1,011
Interesting read. Somehow I've managed not to find the time to look into postgresql. It is annoying to read about it and sit with sql server that microsoft seams to take ages to update.
Post #993489
Posted Monday, September 27, 2010 9:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:38 AM
Points: 1,182, Visits: 1,972
Having worked with Oracle for 25+ years, I'm continually frustrated with the lack of "core" functionality that Microsoft is so slow to implement. This is just another one as Oracle implemented the windowing functionality quite a while ago. Microsoft spends more time on the "eye candy" than the stuff that really matters. It's like designing more cup holders for a car rather than what is "under the hood".


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #993734
Posted Monday, September 27, 2010 5:53 PM


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
Makes sense to me - time to visit the Connect items.



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 #994129
Posted Tuesday, September 28, 2010 9:02 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 5:56 PM
Points: 33,202, Visits: 15,348
Mauve (9/27/2010)
Having worked with Oracle for 25+ years, I'm continually frustrated with the lack of "core" functionality that Microsoft is so slow to implement. This is just another one as Oracle implemented the windowing functionality quite a while ago. Microsoft spends more time on the "eye candy" than the stuff that really matters. It's like designing more cup holders for a car rather than what is "under the hood".


Is it "core"?

I have to say that I haven't missed these functions often. Perhaps it's ignorance of what they can do for me, but I haven't seen this as a big issue. More a nice to have.

I have yet to see people give me a good, wide range of where these things help in an every day situation. Will this help a lot of people regularly? I have asked some of the T-SQL people that I respect and have pushed for this, but they haven't given me enough practical examples. It seems that this is something that will help in some domains, but I'm not sure it's a lot of domains.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #994558
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse