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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2014 Visits: 2971
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1944 Visits: 2436
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
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9811 Visits: 10569
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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2014 Visits: 2971
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
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9811 Visits: 10569
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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2680 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
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1742 Visits: 2054
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-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32252 Visits: 18552
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 (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62356 Visits: 19102
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