April 13, 2010 at 2:04 pm
Hugo Kornelis (4/13/2010)
I only got a point because I (rightly) assumed that the question's author fell victim to the most common misunderstanding about NULL.
This is totally off topic, but being able to understand what a person meant beyond just what they said or wrote is a very important skill to have. How many times has someone come to you and said, "I need X", but after digging into why they want it, you discover what they really need is XY or even W. So often people have just a good enough grasp of databases to think they know what they want, but what they really need is frequently something they never even considered.
BTW - I use this regularly when working on the QOD (well, as best I can anyway). I'm always thinking, "why are they asking this - what are they really trying to test?", and it has saved me once or twice.
Chad
April 13, 2010 at 2:56 pm
mtillman-921105 (4/13/2010)
Hugo Kornelis (4/13/2010)
mtillman-921105 (4/13/2010)
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.I don't know what "real world" you are in, but I do know that your "we" does not include me.
If I want to store something that, when added to 5, yields 5, I'll store 0. I don't need NULL for that. I use NULL as it is intended - for marking the absence of a value. Or rather, since values represents facts about entities in the real world, the absence of a specific fact about a specific entity.
And if you think that 5 + the number of my children is 5, be my guest - but I prefer my database to admit to me that it can't calculate the outcome of that sum.
OK, fair enough. Then how do you handle the nulls in your numeric fields where you want to add and you want the nulls to = 0? I end up putting a "Denullify" everywhere e.g., SELECT x = ISNULL(x, 0). It works, but it gets old to do that for most every numeric column.
*IF* I want to add and want to treat NULL as 0, then I use COALESCE (not ISNULL - but that is another discussion). But that happens rather rarely. Far more common is the use of COALESCE to replace NULL with some human-interpretable placeholder for reports.
And frankly - if you have NULL in most every numeric columnm, then there is a large likelihood that the database design is, to put it politely, in need of improvement.
Judging from the total of your replies in the discussion, I get the feeling that you inherited a database that was the result of an end user who wanted the ability to tab over fields that were 0 (zero), and a front end designer who was too lazy to have the front end code translate that empty field to the value 0 where appropriate. The person who was DBA at that time was unable or unwilling to stand up against this, and now you are stuck with the results.
I know that this happens; you are far from the only one. But don't overgeneralize from your experiences; there are also lots of databases where only a few columns allow NULL, and they use NULL (mostly) as intended.
April 13, 2010 at 5:18 pm
This is a poorly worded question with questionable options. I only answered it "correctly" because I wanted my point, and the author's desire to have "UNKNOWN" as the right answer was easy to reveal. Still, it does not feel right to feed the system with a desired answer just to grab that point while clearly understanding that the answer is plain wrong.
On the upside, the question prompted an excellent post by Hugo and originated a good discussion, so it served its purpose after all.
Oleg
April 13, 2010 at 5:40 pm
Hugo Kornelis (4/13/2010)
"Assuming ANSI_NULLS are on in your SQL Server database, which they are by default..."Okay, I admit it - this snippet in the explanation is actually correct.
You'll be relieved to hear that even this bit is wrong - like all the ON/OFF options, the default is OFF.
Pretty much every client tool that connects to SQL Server sets it ON at connection time, but the default is still OFF :w00t:
Strange question today - and one of the worst I have encountered.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 13, 2010 at 6:35 pm
Paul White NZ (4/13/2010)
Hugo Kornelis (4/13/2010)
"Assuming ANSI_NULLS are on in your SQL Server database, which they are by default..."Okay, I admit it - this snippet in the explanation is actually correct.
You'll be relieved to hear that even this bit is wrong - like all the ON/OFF options, the default is OFF.
Pretty much every client tool that connects to SQL Server sets it ON at connection time, but the default is still OFF :w00t:
Strange question today - and one of the worst I have encountered.
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 🙂
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
April 13, 2010 at 8:19 pm
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:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 14, 2010 at 6:22 am
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.
April 14, 2010 at 9:28 am
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.'
April 14, 2010 at 9:38 am
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 OFFIF 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-2 varchar(1) -- it may be parameter to stored proc sent with NULL value
SELECT * FROM table where column = @v-2
April 15, 2010 at 8:35 am
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
April 15, 2010 at 9:54 am
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".
April 15, 2010 at 10:06 am
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.
April 15, 2010 at 4:35 pm
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.
April 16, 2010 at 1:17 pm
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
April 16, 2010 at 1:19 pm
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
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply