Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Values <> null Expand / Collapse
Author
Message
Posted Saturday, May 8, 2010 11:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 18, 2011 5:53 AM
Points: 73, Visits: 21
Comments posted to this topic are about the item Values <> null
Post #918525
Posted Sunday, May 9, 2010 6:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 10, 2010 11:23 PM
Points: 262, Visits: 53
We can set ansi_nulls off then use <> ,=
Post #918635
Posted Sunday, May 9, 2010 9:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, February 4, 2015 2:43 AM
Points: 2,953, Visits: 440
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
Post #918760
Posted Sunday, May 9, 2010 11:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, August 3, 2015 7:30 AM
Points: 3,244, Visits: 5,043
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 here

Post #918787
Posted Sunday, May 9, 2010 11:14 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, February 4, 2015 2:43 AM
Points: 2,953, Visits: 440
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
Post #918791
Posted Monday, May 10, 2010 12:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 12, 2011 5:09 AM
Points: 28, Visits: 50
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
Post #918814
Posted Monday, May 10, 2010 2:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, Visits: 863
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
Post #918854
Posted Monday, May 10, 2010 4:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 31, 2015 1:51 PM
Points: 1,205, Visits: 797
it's all depends on ANSI_NULLS OFF/ON

Post #918897
Posted Monday, May 10, 2010 6:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 26, 2015 7:10 AM
Points: 2,468, Visits: 8,053
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Post #918925
Posted Monday, May 10, 2010 6:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, August 3, 2015 7:30 AM
Points: 3,244, Visits: 5,043

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 here

Post #918934
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse