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: Friday, September 19, 2014 2:26 AM
Points: 2,953, Visits: 439
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: Saturday, November 29, 2014 3:55 PM
Points: 3,244, Visits: 5,010
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: Friday, September 19, 2014 2:26 AM
Points: 2,953, Visits: 439
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: Wednesday, December 10, 2014 10:49 AM
Points: 1,194, Visits: 791
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: 2 days ago @ 1:30 AM
Points: 2,386, Visits: 7,622
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.


Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
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: Saturday, November 29, 2014 3:55 PM
Points: 3,244, Visits: 5,010

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