• mtillman-921105 (12/15/2010)


    Mike Dougherty-384281 (12/14/2010)


    mtillman-921105 (12/14/2010)


    Good question - thanks! NULLs can be hazardous, they should come with a warning.

    In the case of "Null value is eliminated by an aggregate or other SET operation" the trick is to use nulls without the warning.

    hint: set ANSI_WARNINGS OFF (and accept responsibility for the consequences)

    My solution is SET myColumn = ISNULL(myColumn, 0) everywhere. NULLs are more trouble than they're worth more often than not when dealing with financial data. 😉 (Yes, I know that a NULL can mean that the data's not right. But more often than not, a zero tells me the same thing anyway.)

    Wow!

    And how do you then distinguish a "missing data" 0 from a "number zero" 0?

    BTW, if I'm running AVG over a column, I'd rather have a warning and the average of all non-missing values, than a number that is way too low because the missing values are replaced with 0.


    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/