The Semantics of NULL in SQL Server 2008

  • Comments posted to this topic are about the item The Semantics of NULL in SQL Server 2008

  • Nice article. It summarizes a lot of pitfalls, but I would've liked a paragraph on the behaviour of NULL in COUNT(*) and COUNT(DISTINCT *).

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

  • Nice article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for "empty" or "unknown". This will save you keystrokes, especially when you want to check whether a column does not have a certain value. However, you will anyway probably have to write logic for when columns have the designated default values.

    TERRIBLE!

    VERY TERRIBLE!

  • ajs-1102829 (8/23/2010)


    Comments posted to this topic are about the item <A HREF="/articles/T-SQL/70808/">The Semantics of NULL in SQL Server 2008</A>

    SQL2008???

    What's the difference from the other versions of sqlserver or ANSI SQL?

  • Isn't ANSI_NULLS set to OFF by default? So the behaviour in this article will only apply if you've explicitly set it to ON. I'd have thought that deserved more than a single-line comment right at the end!

  • I would be interested to know what the impact of using NULL is on the storage space required for a Column. i.e. does a nullable nvarchar column save space over a non-nullable nvarchar column with an empty string in it?

  • paul.knibbs (8/24/2010)


    Isn't ANSI_NULLS set to OFF by default? So the behaviour in this article will only apply if you've explicitly set it to ON. I'd have thought that deserved more than a single-line comment right at the end!

    The article says this: "The handling of NULLs as we have summarized follows the ANSI standard. However, Transact-SQL offers an extension for null processing: If the option ANSI_NULLS is set to OFF, comparisons between nulls, such as NULL = NULL, evaluate to TRUE." Quite correctly, this implies that ANSI_NULLS is ON by default, and only turned off if that's what you specify.

    John

  • Carlo Romagnano (8/24/2010)


    One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for "empty" or "unknown". This will save you keystrokes, especially when you want to check whether a column does not have a certain value. However, you will anyway probably have to write logic for when columns have the designated default values.

    TERRIBLE!

    VERY TERRIBLE!

    Constructive feedback, lalalalala.

    Please elaborate why this is so terrible, that way we can all learn something.

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

  • Quite correctly, this implies that ANSI_NULLS is ON by default, and only turned off if that's what you specify.

    John

    I understood the situation to be the opposite--e.g. ANSI_NULLS is OFF by default. The description of the SET ANSI_NULLS command in Books Online appears to agree with that, too.

  • Paul

    You're quite right - Books Online (2008 R2) says: "The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set ANSI_NULLS to ON when connecting. This setting can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to an instance of SQL Server. The default for SET ANSI_NULLS is OFF." When I connect through SSMS, ANSI_NULLS is set to ON - this must be the OLE DB Provider setting this for me.

    Interestingly, the same topic in Books Online says that in a future version of SQL Server, it will not be possible to set ANSI_NULLS to OFF.

    John

  • I rated this article "poor". A lot of the information is correct, and relevant - but there is a very important error, right at the start of the article.

    The author writes:

    A NULL value in SQL Server is (by design) meant to indicate an unknown or undefined value

    And this is absolutely not true. (Though it is indeed described this way in Books Online).

    The implementation of NULL in SQL Server (not only SQL Server 2008!!) follows the ANSI standard. And the ANSI standard defines "the null value" as:

    null value: A special value that is used to indicate the absence of any data value.

    The key words here are "absence of any data value". Or, as I usually describe it: NULL represents missing data.

    NULL does therefore NOT represent unknown data. Of course, the reason that data is missing might be that it is unknown (patient still in coma and unidentified; name, birthdate, etc are all unknown). But it might also be that it is inapplicable (when doing prenatal surgery, the patient has no name or birthday yet). Or it might be known, but totally irrelevant (there are good reasons to register the number of children a female patients has put into the world; for male patients, it's totally irrelevant in a medical database).


    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/

  • da-zero (8/24/2010)


    I would've liked a paragraph on the behaviour of NULL in COUNT(*) and COUNT(DISTINCT *).

    There's not much to say about that, as COUNT(*) does not care if values are NULL or not (it simply counts rows), and COUNT(DISTINCT *) is a syntax error.

    In COUNT(columnname), NULL is relevant. If you demand a COUNT(Age) from the patients table, you are not requesting the number of patients in the database, but the number of ages in the database. Since NULL represents missing data, a row with a NULL age does not represent an age in the database. These rows are correctly excluded from the COUNT() result.


    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/

  • Carlo Romagnano (8/24/2010)


    One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for "empty" or "unknown". This will save you keystrokes, especially when you want to check whether a column does not have a certain value. However, you will anyway probably have to write logic for when columns have the designated default values.

    TERRIBLE!

    VERY TERRIBLE!

    Agreed.

    How NULL behaves might cause some unexpected results, but far less from how such "magic values" behave.

    I'd say that, as long as you use NULLs as intended (i.e., to represent missing data without inferring anything about the reason it might be missing), how SQL Server treats the NULL values will in 99% of all cases match what humans would do when asked to operate on incomplete data. All without any special extra handling. Using "magic values" means you have to write special handling to mimic default NULL behaviour in those 99% of all cases.


    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/

  • da-zero (8/24/2010)


    Carlo Romagnano (8/24/2010)


    One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for "empty" or "unknown". This will save you keystrokes, especially when you want to check whether a column does not have a certain value. However, you will anyway probably have to write logic for when columns have the designated default values.

    TERRIBLE!

    VERY TERRIBLE!

    Constructive feedback, lalalalala.

    Please elaborate why this is so terrible, that way we can all learn something.

    First: problem for storage

    Second: how can you determine if a value of a column has been specified? (e.g. a price of an article: 0 means it's free or the price has not been established?).

    Third:

    One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs

    that's not true. Think OUTTER JOIN, the column could be null, although is declared NOT NULLable!

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

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