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 » SQLServerCentral.com » Editorials » SQL Server's implementation of Window...
Add to briefcase
14 posts, Page 1 of 2
12»»

SQL Server's implementation of Window Functions Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Phil Factor
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: Sunday, May 19, 2013 4:00 AM
Points: 533, Visits: 2,285
Comments posted to this topic are about the item SQL Server's implementation of Window Functions

Best wishes,

Phil Factor
Simple Talk
Post #993279
chrisn-585491
Posted Sunday, September 26, 2010 10:38 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:32 AM
Points: 479, Visits: 1,263
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
WayneS
Posted Sunday, September 26, 2010 11:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
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
Phil Factor
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: Sunday, May 19, 2013 4:00 AM
Points: 533, Visits: 2,285
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
WayneS
Posted Sunday, September 26, 2010 2:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
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
UMG Developer
Posted Sunday, September 26, 2010 3:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148
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
IceDread
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: Friday, November 16, 2012 3:47 AM
Points: 290, Visits: 988
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
Mauve
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: Monday, May 20, 2013 10:43 AM
Points: 1,146, Visits: 1,848
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
SQLRNNR
Posted Monday, September 27, 2010 5:53 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337
Makes sense to me - time to visit the Connect items.


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 #994129
Steve Jones - SSC Editor
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: Today @ 11:20 AM
Points: 31,437, Visits: 13,752
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
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

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