SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by lloyd monteiro / Values <> null / Latest Posts

RE: Values <> null

IgorMi — Wed, 21 Mar 2012 11:28:56 GMT

select * from ##TableNulls where col2 is not null will give out the expected results at fist sightThank you for the question

RE: Values <> null

zymos — Mon, 19 Mar 2012 10:00:15 GMT

Interesting topic, yet confusing at times when determining times to set the ANSI_NULL to ON or OFF to yield expected results. Thanks.

RE: Values <> null

amit_adarsh — Wed, 19 May 2010 06:56:58 GMT

its all about the database setting of ANSI_NULLS Set ANSI_NULLS off / on But by default database settings for ANSI_NULLS is ON so you will get nothing .

RE: Values <> null

John McC — Mon, 17 May 2010 06:12:15 GMT

Remember:Never assume anything because it can make a [b]donkey[/b] out of [b]u[/b] and [b]me[/b]Won't let me SUBSTRING('assume',1,3) :-D

RE: Values <> null

lloyd-236007 — Tue, 11 May 2010 01:05:19 GMT

yes actually i had that question with default settings in mind, but its good to see so many responses as i know it has actually set minds thinking and after all thats the objective.In future i will include things like this.

RE: Values <> null

sdorris-1150234 — Mon, 10 May 2010 13:46:43 GMT

Ahhhh...that's why you call us McNewbie's. :-) Great site, and great editorials. Look forward to seeing them every day.

RE: Values <> null

Steve Jones - SSC Editor — Mon, 10 May 2010 13:41:19 GMT

We can't edit the question in email. If we find an issue, it's corrected on the site.

RE: Values <> null

sdorris-1150234 — Mon, 10 May 2010 13:38:27 GMT

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.

RE: Values <> null

SQLRNNR — Mon, 10 May 2010 10:24:57 GMT

Nice question and thanks for updating it as well Steve.

RE: Values <> null

Steve Jones - SSC Editor — Mon, 10 May 2010 10:00:13 GMT

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

RE: Values <> null

Trey Staker — Mon, 10 May 2010 09:19:22 GMT

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.

RE: Values <> null

Daniel Bowlin — Mon, 10 May 2010 08:09:39 GMT

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

RE: Values <> null

Atif-ullah Sheikh — Mon, 10 May 2010 06:36:19 GMT

[quote]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. [/quote]Thats what these questions are all about. Small things that slips from our minds...:-)

RE: Values <> null

Cadavre — Mon, 10 May 2010 06:08:36 GMT

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.

RE: Values <> null

sharath.chalamgari — Mon, 10 May 2010 04:30:23 GMT

it's all depends on ANSI_NULLS OFF/ON

RE: Values <> null

deepak.a — Mon, 10 May 2010 02:08:12 GMT

The Result depends on set optionSET ANSI_NULLS OFFselect * from ##TableNulls where col2 <> null -- This will give ResultSET ANSI_NULLS ONselect * from ##TableNulls where col2 <> null -- this will not give result

RE: Values <> null

muhammad.mazhar — Mon, 10 May 2010 00:47:48 GMT

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

RE: Values <> null

Iggy-SQL — Sun, 09 May 2010 23:14:32 GMT

[quote][b]Atif Sheikh (5/9/2010)[/b][hr]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.[/quote]Ah true, I forgot about the different default settings between database and SSMS (or Query Analyzer for SQL2000). I stand corrected.

RE: Values <> null

Atif-ullah Sheikh — Sun, 09 May 2010 23:01:44 GMT

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.

RE: Values <> null

Iggy-SQL — Sun, 09 May 2010 21:41:54 GMT

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

RE: Values <> null

Ali Asghar Sharifi — Sun, 09 May 2010 06:24:00 GMT

We can set ansi_nulls off then use <> ,=

Values <> null

lloyd-236007 — Sat, 08 May 2010 11:13:07 GMT

Comments posted to this topic are about the item [B]Values <> null[/B]