January 7, 2016 at 11:51 am
Koen Verbeeck (1/7/2016)
Ed Wagner (1/7/2016)
Koen Verbeeck (1/7/2016)
Ed Wagner (1/7/2016)
Great (yet simple) illustration of a concept I use frequently. I did not know that the ORDER BY clause was optional, so I learned something new. Thanks.That's very important though!
If you specify an ORDER BY clause, you have a windowing function as introduced in SQL Server 2012. Which means you have the default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is a terrible default performance wise, but it can also lead to unexpected results.
If you don't specify an ORDER BY clause, you have the "traditional" window function as defined in SQL 2005, which is useful for calculating subtotals and totals.
More info:
Right - the totals and subtotals is the exact scenario I was thinking of. I agree with your point about the default, but at least we have the flexibility. Personally, I think the windowing functions are one of the most useful things they've introduced
in the past 10 yearssince SQL 2005. Just another tool in the toolbox, but a highly useful one.They are immensely useful in writing BI queries. I can't imagine my job without window functions.
I had to do without them for a decade... 🙂
January 7, 2016 at 12:10 pm
Hugo Kornelis (1/7/2016)
Rune Bivrin (1/7/2016)
Interestingly enough, the most recent docs for COUNT() states that the OVER() requires the ORDER BY clause, which is patently untrue, at least up to SQL 2012....Good discovery! I have forwarded your comment to Microsoft - my experience is that this kind of mistakes are usually corrected pretty quickly.
Also note that the syntax diagram suggests that not only the ORDER BY clause, but also the OVER clause itself is mandatory (though the text of the article does state that it's optional).
I just got a note from Microsoft that they have fixed this already.
Thanks for reporting this mistake!
January 7, 2016 at 1:43 pm
Koen Verbeeck (1/7/2016)
Ed Wagner (1/7/2016)
Koen Verbeeck (1/7/2016)
Ed Wagner (1/7/2016)
Great (yet simple) illustration of a concept I use frequently. I did not know that the ORDER BY clause was optional, so I learned something new. Thanks.That's very important though!
If you specify an ORDER BY clause, you have a windowing function as introduced in SQL Server 2012. Which means you have the default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is a terrible default performance wise, but it can also lead to unexpected results.
If you don't specify an ORDER BY clause, you have the "traditional" window function as defined in SQL 2005, which is useful for calculating subtotals and totals.
More info:
Right - the totals and subtotals is the exact scenario I was thinking of. I agree with your point about the default, but at least we have the flexibility. Personally, I think the windowing functions are one of the most useful things they've introduced
in the past 10 yearssince SQL 2005. Just another tool in the toolbox, but a highly useful one.They are immensely useful in writing BI queries. I can't imagine my job without window functions.
I don't write BI queries and I can't imagine life without them either. 😉 When you think about what they do under the hood, they're pretty impressive.
January 7, 2016 at 1:58 pm
Hugo Kornelis (1/7/2016)
Hugo Kornelis (1/7/2016)
Rune Bivrin (1/7/2016)
Interestingly enough, the most recent docs for COUNT() states that the OVER() requires the ORDER BY clause, which is patently untrue, at least up to SQL 2012....Good discovery! I have forwarded your comment to Microsoft - my experience is that this kind of mistakes are usually corrected pretty quickly.
Also note that the syntax diagram suggests that not only the ORDER BY clause, but also the OVER clause itself is mandatory (though the text of the article does state that it's optional).
I just got a note from Microsoft that they have fixed this already.
Thanks for reporting this mistake!
A small step for a man, a giant leap for man kind. :hehe:
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply