Group By, Count, and NULLs

  • twin.devil

    SSC-Insane

    Points: 22208

    interesting question. thanks for sharing

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    Thomas Abraham (2/2/2015)


    Nice question to highlight the differences in ways that NULLs are handled by different operations. Thanks!

    +1

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    psingla (2/2/2015)


    Good Question...

    That's why I prefer using count(1)

    I use COUNT(0). Any difference?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Iwas Bornready (4/14/2016)


    psingla (2/2/2015)


    Good Question...

    That's why I prefer using count(1)

    I use COUNT(0). Any difference?

    No.

    The COUNT function has officially two forms. The first is COUNT(*) which self-documents that it counts rows rather any specific constant of the row,

    The other form is officially COUNT(expression), which logically states that the expression has to be evaluated for every row and the non-NULL results have to be counted. COUNT(0) and COUNT(1) are examples of this where the expression is very simple. In SQL Server, the optimizer realizes that the expressions 0 and/or 1 can never become NULL so it short-circuits the expression evaluation and simply uses the same logic as for COUNT(*).

    Another common form of COUNT(expression) is to use a simple column name as the expression. For a nullable column, this means that the RDBMS has to actually count the number of rows where this column is not NULL. However, if the column is nullable then SQL Server again realizes that it can simply pretend you used COUNT(*).

    COUNT with an expression that is more complex than any of these examples is permitted, but rare (and also very rarely useful). The more complex the expression is, the lower the chance that SQL Server can adequately determine if a NULL result is even possible. If it cannot determine this, then no short-circuiting is possible and the expression will always be evaluated for every row.


    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/

Viewing 4 posts - 16 through 19 (of 19 total)

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