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 «««34567»»

NULL Expand / Collapse
Author
Message
Posted Tuesday, April 13, 2010 8:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:59 AM
Points: 11,194, Visits: 11,166
Peter Trast (4/13/2010)
So if I connect via command line, I can query the status of all of my ON/OFF options and they will all be off until I connect... SSMS or Redgate? Interesting. I will have to test that

I'm afraid I'm not sure what you mean by connecting from the command line...

Tools/libraries/drivers typically SET various options when they connect - including ANSI_NULLS.

My point is that SQL Server's defaults (out of the box, as it were) are for all the ON/OFF options to be OFF at both server and database level. You can change this, but that's the default.

Instance-level defaults are set using sp_configure 'user options'.
Database-level defaults are set using ALTER DATABASE <sql_option>.

See:

Using Options in SQL Server
ALTER DATABASE SET Options
user options Option




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #902843
Posted Wednesday, April 14, 2010 6:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 5:43 AM
Points: 48, Visits: 203
This whole discussion is NULL and VOID since NULL is no longer being supported in SQL Server. Oh wait, that was just an April Fool's joke. Sorry.
Post #903052
Posted Wednesday, April 14, 2010 9:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:51 PM
Points: 880, Visits: 607
Hugo, 5+the number of your children is obviously 7... I think maybe you messed up your inner select :)

I think that this does illustrate a point that has not been made yet: Null as a value in SQL server is used to indicate more than one thing: the empty set, and the absence of a scalar value.
I think people often focus on the peculiarities of using Null as a scalar value (Null <> Null, intransitive comparisons) However Null makes a lot more sense when looking at the logical concept of an empty set.

What do I get when I Select Hugo's grandchildren? A set with no rows. What is the favorite color of Hugo's grandchild? Null




Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Post #903263
Posted Wednesday, April 14, 2010 9:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:31 PM
Points: 6, Visits: 38
Tao Klerks
The sentence "NULL does not equal NULL" is correct, when you have ANSI_NULLs on...

On the other hand, the statement "Try comparing two NULL values in SQL - they will never be equal" is just wrong. Try this:
SET ANSI_NULLS OFF
IF Null = Null
PRINT 'Oops, Null equals Null today!'



Microsoft is going to eliminate ANSI_NULLS OFF with the next version of SQL Server. If they do it, then most of stored proc that compare table columns against variables may need to be rewritten because the variables may have NULL values

--Example:
--this statement will be nonfunctional if compiled with ANSI_NULLS ON:
DECLARE @v varchar(1) -- it may be parameter to stored proc sent with NULL value
SELECT * FROM table where column = @v
Post #903270
Posted Thursday, April 15, 2010 8:35 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: Monday, May 21, 2012 3:13 PM
Points: 516, Visits: 1,563
I think it would have been delicious to have one of the options for the answer to be "I Don't Know" and it be the correct response.

Steve



Post #904077
Posted Thursday, April 15, 2010 9:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
steve block (4/15/2010)
I think it would have been delicious to have one of the options for the answer to be "I Don't Know" and it be the correct response.

Steve
Excellent! And, may I suggest another (incorrect) choice to round out the list? "There is no answer".
Post #904166
Posted Thursday, April 15, 2010 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:31 PM
Points: 6, Visits: 38
john.arnott (4/15/2010)
steve block (4/15/2010)
I think it would have been delicious to have one of the options for the answer to be "I Don't Know" and it be the correct response.

Steve
Excellent! And, may I suggest another (incorrect) choice to round out the list? "There is no answer".


Does Microsoft realize what the do? That it is going to be quite expensive to rewrite everything that is relying on ANSI_NULLS OFF? It is going to be a nightmare to rewrite complex T-SQL logic encapsulated in the scripts, functions, stored procs, etc.
Post #904187
Posted Thursday, April 15, 2010 4:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:07 PM
Points: 6,002, Visits: 8,266
alexkedrov (4/15/2010)
john.arnott (4/15/2010)
steve block (4/15/2010)
I think it would have been delicious to have one of the options for the answer to be "I Don't Know" and it be the correct response.

Steve
Excellent! And, may I suggest another (incorrect) choice to round out the list? "There is no answer".


Does Microsoft realize what the do? That it is going to be quite expensive to rewrite everything that is relying on ANSI_NULLS OFF? It is going to be a nightmare to rewrite complex T-SQL logic encapsulated in the scripts, functions, stored procs, etc.

Yes, I think Microsoft does realize that. That is exactly why changes like this are announced years in advance.

From Books Online for SQL Server 2005:
"Important:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."

From Books Online for SQL Server 2008:
"Important:
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."

Based on my experience with previous removed features, I expext that the next version of SQL Server will introduce the new behaviour, but maintain the old behaviour if you set the compatibulity level to SQL2008 or below; that will then remain the case until the compatibility level for SQL Server 2008 is removed (which will be at least two or three major releases after the next one).

So we got our first warning in 2005, and the feature will probably not be completely gone before 2020 or so. That is 15 years - if that is not enough time, then I don't know what is.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #904473
Posted Friday, April 16, 2010 1:17 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 8,834, Visits: 9,391
I liked Hugo's comment better than the explanation or the answer or the question (although, like Hugo, I got the point by guessing that the author wouldn't want the answer to be NULL).


Tom
Post #905163
Posted Friday, April 16, 2010 1:19 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 8,834, Visits: 9,391
mtillman-921105 (4/13/2010)
Speaking of tangents (sort of)...
I understand logically that NULL (as an unknown quantity) added or used in any other mathematical formula should = NULL.

But I still wish that SQL would use it as a zero since in the real world when we add 5 + NULL we want to see 5, not NULL. How often is NULL actually used as it is intended anyway in this context?


I don't know which real world you live in, but I reckon it sure aint this planet Earth!



Tom
Post #905167
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse