Values <> null

  • Comments posted to this topic are about the item Values <> null

  • We can set ansi_nulls off then use <> ,=

  • The result would depend on the database settings for ANSI_NULLS, if the database settings for ANSI_NULLS is set to ON, then the answer given is correct, but if it is set to OFF, then the answer given is incorrect (and A is the correct answer).

    Whilst I have answered correctly because I made a guess that the default database settings is used, some other users running the query might get the answer A if their database settings for ANSI_NULLS is OFF (with or without their knowledge).


    Urbis, an urban transformation company

  • Set ANSI_NULLS off / on changes the results.

    By default in database settings it is set to OFF.

    But in SSMS --> Tools --> Options, go to Query Execution --> SQL Server --> ANSI Tab. Here it is set to On. Means, for the query editor window, settings is set to On BY DEFAULT.

    Bit Confusing. 🙂

    And I go the point.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Atif Sheikh (5/9/2010)


    Set ANSI_NULLS off / on changes the results.

    By default in database settings it is set to OFF.

    But in SSMS --> Tools --> Options, go to Query Execution --> SQL Server --> ANSI Tab. Here it is set to On. Means, for the query editor window, settings is set to On BY DEFAULT.

    Bit Confusing. 🙂

    And I go the point.

    Ah true, I forgot about the different default settings between database and SSMS (or Query Analyzer for SQL2000). I stand corrected.


    Urbis, an urban transformation company

  • I got it right as i knew that <> null doesn't work in that scenario, but if one would use 'is not null' instead of <> null, it would get the result.

    Regards,

    Mazhar Karimi

  • The Result depends on set option

    SET ANSI_NULLS OFF

    select * from ##TableNulls where col2 <> null -- This will give Result

    SET ANSI_NULLS ON

    select * from ##TableNulls where col2 <> null -- this will not give result

  • it's all depends on ANSI_NULLS OFF/ON

  • Since ANSI_NULLS affects the result, and since the default means we get the desired option - only those that simply input the query are going to be annoyed. Anyone that understood the question will have assumed default options were there and get the correct answer.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Atif Sheikh (5/9/2010)

    --------------------------------------------------------------------------------

    Set ANSI_NULLS off / on changes the results.

    By default in database settings it is set to OFF.

    But in SSMS --> Tools --> Options, go to Query Execution --> SQL Server --> ANSI Tab. Here it is set to On. Means, for the query editor window, settings is set to On BY DEFAULT.

    Bit Confusing.

    And I go the point.

    Ah true, I forgot about the different default settings between database and SSMS (or Query Analyzer for SQL2000). I stand corrected.

    Thats what these questions are all about. Small things that slips from our minds...:-)

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • I love all these questions and discussions about so many of the smaller (but usually critical to understand) details. They really make me think, and definitely make me better whether I got the question right or wrong.:-)

  • Good question but it is a variation on a theme we've had as QOD in the past. I wish the Questioner would have included a few more details on Ansi NULL ON vs OFF.

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • The question has been edited to include ANSI NULLS set on.

  • Nice question and thanks for updating it as well Steve.

    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

  • I was sure I had the correct answer based on the question in the email...but when I linked to the site I saw another question - "What is the output of this query if ANSI NULLS are set to ON?". Was this a side question, or part of the original question? At this point, I wasn't sure which one was the actual question, so I got it wrong. A bit confusing.

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

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