Post reply

Values <> null

  • May 8, 2010 at 11:13 am

    #221308

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

  • May 9, 2010 at 6:24 am

    #1164683

    We can set ansi_nulls off then use <> ,=

  • May 9, 2010 at 9:41 pm

    #1164790

    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

  • May 9, 2010 at 11:01 pm

    #1164815

    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]

  • May 9, 2010 at 11:14 pm

    #1164819

    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

  • May 10, 2010 at 12:47 am

    #1164836

    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

  • May 10, 2010 at 2:08 am

    #1164872

    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

  • May 10, 2010 at 4:30 am

    #1164902

    it's all depends on ANSI_NULLS OFF/ON

  • May 10, 2010 at 6:08 am

    #1164920

    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/

  • May 10, 2010 at 6:36 am

    #1164926

    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]

  • May 10, 2010 at 8:09 am

    #1165006

    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.:-)

  • May 10, 2010 at 9:19 am

    #1165057

    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

  • May 10, 2010 at 10:00 am

    #1165089

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

  • May 10, 2010 at 10:24 am

    #1165109

    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

  • May 10, 2010 at 1:38 pm

    #1165205

    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 21 total)

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