SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NULL


NULL

Author
Message
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34184 Visits: 11359
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... Unsure

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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Dennis Wagner-347763
Dennis Wagner-347763
SSC Veteran
SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)

Group: General Forum Members
Points: 262 Visits: 228
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.
weitzera
weitzera
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1344 Visits: 629
Hugo, 5+the number of your children is obviously 7... I think maybe you messed up your inner select Smile

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

alexkedrov
alexkedrov
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 69
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
steve block
steve block
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1862 Visits: 1563
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



john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3512 Visits: 3059
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".
alexkedrov
alexkedrov
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 69
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.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18377 Visits: 12426
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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25344 Visits: 12489
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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25344 Visits: 12489
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?


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

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search