SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server's implementation of Window Functions


SQL Server's implementation of Window Functions

Author
Message
Phil Factor
Phil Factor
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4918 Visits: 3031
Comments posted to this topic are about the item SQL Server's implementation of Window Functions


Best wishes,

Phil Factor
Simple Talk
chrisn-585491
chrisn-585491
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3944 Visits: 2565
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.
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21974 Visits: 10655
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
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, 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

Phil Factor
Phil Factor
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4918 Visits: 3031
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
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21974 Visits: 10655
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
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, 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

UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4088 Visits: 2204
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...
IceDread
IceDread
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 1145
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.
Mauve
Mauve
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3390 Visits: 2066
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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67621 Visits: 18570
Makes sense to me - time to visit the Connect items.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148647 Visits: 19446
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search