Get Your ANSI_NULLs Settings Consistent

  • Bill Talada

    SSChampion

    Points: 11956

    Comments posted to this topic are about the item Get Your ANSI_NULLs Settings Consistent

  • Paul White

    SSC Guru

    Points: 150341

    An interesting article on an important subject. I would have rated it 5 stars instead of 4 if it had gone further in explaining what might need to be done to fix potential problems - sadly it is not as simple as replacing = NULL with IS NULL.

    ANSI_NULLS OFF behaviour is decidedly odd, and often counter-intuitive, no wonder Microsoft are keen to stop supporting it. I have an example of this type of weirdness pending as a Question of the Day (QotD) so I'll just say that one example of weirdness involves IN and NOT IN...

  • SQLRNNR

    SSC Guru

    Points: 281210

    Good article. I couldn't add anything more than what Paul has already indicated. Maybe a follow-up article on the same subject to go along with that QOD?;-)

    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

  • Paul White

    SSC Guru

    Points: 150341

    CirquedeSQLeil (1/26/2010)


    Good article. I couldn't add anything more than what Paul has already indicated. Maybe a follow-up article on the same subject to go along with that QOD?;-)

    Good idea...

  • xdream

    Valued Member

    Points: 58

    Nice article, just followed your steps on our databases. 😀

    Though for general purpose, I'm missing some short explanations on ANSI settings and as you check them in step 2) and it's not only about ANSI NULL.

    In step 4) I first was kind of surprised as the sql-statement will find other "= NULL"s, too (e.g. SET @Var = NULL). But it shows a starting point for checking your code automatically and fast.

    Best regards

  • Bill Talada

    SSChampion

    Points: 11956

    I'm happy people are finding some value in the article; I had sent in a rough draft to see if it would be a topic of interest but they published it without asking me to tighten it up. My main purpose for writing the article was to show people how to be able to rip out all ANSI type statements from all their scripts and to never have to think again about any odd behavior of nulls on a per script basis.

  • andy.gray

    SSC Journeyman

    Points: 98

    Most interesting but I have a little query.

    Why is it necessary to have a function for comparing nullable ints but not for any other data types? Is there something specific about nullable ints?

    Kind regards

    Andy

  • Bill Talada

    SSChampion

    Points: 11956

    andy.gray (1/27/2010)


    Most interesting but I have a little query.

    Why is it necessary to have a function for comparing nullable ints but not for any other data types? Is there something specific about nullable ints?

    Kind regards

    Andy

    The need for nullable compares is for all data types. I only have three in place for int, varchar, and datetime types so far for my needs. With a simple find/replace you can generate the others.

  • Richard Gibbins

    Old Hand

    Points: 377

    I'm probably stirring up a hornet's nest here but I have a problem with the way you are treating nulls. The whole point of null is that the value is undefined so comparing a value and saying they are equal if both are null is not valid. Two columns and/or variables are equal if and only if the values are defined and equal. Code should be aware of nulls and deal with them but not by equating two null values.

    Richard

  • James Stephens

    SSC-Addicted

    Points: 487

    I have been bitten by the IN/NOT IN null before...it's a tough one that had me scratching my head for an hour or so. That would make a good qod.

    --Jim

  • Paul White

    SSC Guru

    Points: 150341

    Richard Gibbins (1/27/2010)


    The whole point of null is that the value is undefined so comparing a value and saying they are equal if both are null is not valid.

    The currently defined behaviour in SQL Server depends on the setting of ANSI_NULLS - see http://msdn.microsoft.com/en-us/library/ms188048.aspx. Thankfully, we are moving to a point where the statement quoted above will be true some day.

  • Bill Talada

    SSChampion

    Points: 11956

    Richard Gibbins (1/27/2010)


    I'm probably stirring up a hornet's nest here but I have a problem with the way you are treating nulls. The whole point of null is that the value is undefined so comparing a value and saying they are equal if both are null is not valid. Two columns and/or variables are equal if and only if the values are defined and equal. Code should be aware of nulls and deal with them but not by equating two null values.

    Richard

    Well, my function is called NullableIntsMatch - not NullableIntsEqual if that helps you see the value in it. The function is super useful; you can inline the code if you don't like the function. But my question to you is how can you find the rows with unassigned values using a stored procedure without comparing null column values to null parameter values?

  • xdream

    Valued Member

    Points: 58

    So why not use something like "ISNULL(IntValue1, -1) = ISNULL(IntValue2, -1)" for every case where IntValueX has to be >-1? I guess this would be faster than calling a function.

  • Paul White

    SSC Guru

    Points: 150341

    xdream (1/28/2010)


    So why not use something like "ISNULL(IntValue1, -1) = ISNULL(IntValue2, -1)" for every case where IntValueX has to be >-1? I guess this would be faster than calling a function.

    How does that work if -1 is a valid value in the column? 😉

    (IntValue1 <> IntValue2) OR (IntValue1 IS NULL AND IntValue2 IS NULL)

    ...is one correct way to test this type of condition. Just one more reason to avoid NULL values wherever possible.

  • AlreadyPicked

    SSCommitted

    Points: 1786

    It's important to note that for some settings, like "ANSI_NULL_DEFAULT" and "ANSI_NULLS", the ole db connection overrides the database settings:

    "[...] Connection-level settings (set using the SET statement) override the default database setting for ANSI_NULLS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session when connecting to SQL Server."

    ___________________________________
    I love you but you're standing on my foot.

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

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