The Lonely Count

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 718163

    Comments posted to this topic are about the item The Lonely Count

  • This was removed by the editor as SPAM

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy one, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Rune Bivrin

    SSCertifiable

    Points: 7749

    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....


    Just because you're right doesn't mean everybody else is wrong.

  • This was removed by the editor as SPAM

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    I like the question, but there are two small caveats.

    First is that I would have liked the answer to be phrased as "10 in all 10 rows" or something equivalent, to make clear that there is a difference between COUNT with empty OVER() clause and COUNT with no OVER clause at all (which would have returned just a single value 10 if the myid value itself were omitted from the SELECT and a GROUP BY were added).

    Second is that the explanation wrongly states that without ORDER BY, the partition is "ordered in natural order, which is however SQL Server reads the data". This is not true. Without ORDER BY, the partition is unordered, and still considered as a SET. In an unordered partition, the aggregate function is applied to the whole partition, and that is why the COUNT result is 10 for each row.

    In an ordered partition, the aggregate applies to the result of the ROW or RANGE specfication, which default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. So if the explanation would be right about the partition being "ordered in natural order", then the query would have assigned the values 1 through 10 based on that "natural order".


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Rune Bivrin

    SSCertifiable

    Points: 7749

    Hugo Kornelis (1/7/2016)


    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).

    Yeah, I noticed that too, but that was so preposterous I ignored it.


    Just because you're right doesn't mean everybody else is wrong.

  • George Vobr

    SSCrazy Eights

    Points: 9127

    Useful and interesting question, thank you Steve.

    The same result for i may give also this script:

    SELECT

    MyID

    , COUNT(*) OVER (PARTITION BY '') AS i

    -- OVER (PARTITION BY '') is the same as OVER () --

    FROM dbo.MyTable;

  • Ed Wagner

    SSC Guru

    Points: 286976

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good, an easy one, thank you.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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:

    Beware the defaults! (in windowing functions)[/url]

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Ed Wagner

    SSC Guru

    Points: 286976

    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:

    Beware the defaults! (in windowing functions)[/url]

    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 years since SQL 2005. Just another tool in the toolbox, but a highly useful one.

  • Revenant

    SSC-Forever

    Points: 42467

    Interesting question, interesting discussion. Thanks, Steve!

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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:

    Beware the defaults! (in windowing functions)[/url]

    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 years since 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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply