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

The Semantics of NULL in SQL Server 2008 Expand / Collapse
Author
Message
Posted Wednesday, August 25, 2010 12:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:42 PM
Points: 451, Visits: 364
tim.stevens (8/24/2010)
This is one of the best concise treatises on NULL I have seen. Your adminition about a well architected database design not allowing NULLs for any columns is as true as it is bold. The concept of NULL is, quite simply, a flawed one and really has not business being a part of the relational model (See E.F. Codd, The Relational Model For Database Management, ISBN 0-201-14192-2). That aside, having a definitive (and informed) strategy for handling these pesky buggers saves hours of hair-pulling.


You should have mentioned that EF Codd and CJ Date (and others) conducted a very long, very pointed, and very public debate concerning the role of NULL in the relational calculus. As I recall, Date had a column titled "According to Date" in one of the monthly mags (?Database Programming and Design?) from Ziff Davis or Miller Freeman (it has been a long time ).

Certainly there is academic interest in various theoretical ideals such as nth Normal Form, NULL-less schemas, etc. Those of us faced with more practical problems need pragamitic solutions.

We generally accept that something approaching 3rd Normal is adequate and appropriate for most OLTP situations. Similarly, when properly defined and implemented NULL can yield a nice concise data model that is very efficient and safe. As one example, I have found it very useful in relationship tables that must represent mulitple, changing relationships over time. The "Current" department(s) for a broker is the one whose EndDate is NULL. Previous departments are represented with Start and End date values. This particular implementation permitted multiple, overlapping, and concurrent assignments.

Post #975109
Posted Wednesday, August 25, 2010 4:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 9:19 AM
Points: 6, Visits: 37
It would also be helpful to expand a bit on string manipulations using NULL. While this nice article is restricted to the DB Engine, I recently had to dig to find out why SSIS was attempting to insert a null string into a varchar column set to disallow NULL.

Turns out that SSIS string concatenation in the Derived Column Data Task will set the column to NULL if any of the input columns is NULL. The work around is using the SSIS ISNULL(<column>,TRUE, FALSE) for each expression... with TRUE being set to an empty string.... and FALSE being the original column.
Post #975261
Posted Thursday, August 26, 2010 9:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 4, 2010 4:52 PM
Points: 8, Visits: 16
Adolfo J. Socorro
main cause of confusion, I would say, is thinking that NULL means blank or empty.


Microsoft
A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value.


If that is true then, what is an empty value? AFAIK, NULL is the only way to leave a field empty!

I think it would be more reasonable to say that from the point of view of a logical or arithmetic operation a NULL value is considered unknown since it cannot be resolved to an actual value so it is unknown within the context of the operation and that would make the result of the operation unknown as well, but from the point of view of data storage it is actually a blank or empty field.

Socorro
One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for "empty" or "unknown". This will save you keystrokes, especially when you want to check whether a column does not have a certain value.


I don't see how that will save you significant amount of time, checking for a NULL is just as easy as checking for any value and it will make your code more readable. Using a magic number is something you would want to do if you didn't had NULL support, it doesn't have any advantages over NULLs (you'll still have to check for the magic value implicitly to find out if it was set), and it has some disadvantages, of the top of my head:

1) It can screw up greater than/less than queries (the empty fields may come up on the query when they're not supposed to);

2) If you do it on a foreign key then you'll need a dummy record on the referenced table;

3) Most front-end data frameworks can handle NULL values without any special handling, for example you can store a NULL value in any nullable data type or use it to set a GUI control directly, a magic value will always require some special handling;

4) Any qualified developer should understand the concept of NULLs but may not understand the logic behind your magic value.
Post #976110
Posted Thursday, August 26, 2010 9:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 4, 2010 4:52 PM
Points: 8, Visits: 16
Don Gilman, P.E. (8/25/2010)
It would also be helpful to expand a bit on string manipulations using NULL. While this nice article is restricted to the DB Engine, I recently had to dig to find out why SSIS was attempting to insert a null string into a varchar column set to disallow NULL.

Turns out that SSIS string concatenation in the Derived Column Data Task will set the column to NULL if any of the input columns is NULL. The work around is using the SSIS ISNULL(<column>,TRUE, FALSE) for each expression... with TRUE being set to an empty string.... and FALSE being the original column.


The reason for that is the same reason why NULL + 5 = NULL, because NULL is considered unknown in all operations, including string concatenation, if you concatenate an unknown string to a known one the result will logically be unknown or NULL.

He did cover that when he said "Also, any non-logical expressions involving NULLs have an unkown, or NULL, result."
Post #976111
Posted Thursday, August 26, 2010 10:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 7:17 PM
Points: 197, Visits: 459
AFAIK, NULL is the only way to leave a field empty!


I disagree: setting a field to NULL does not 'leave [or make] a field empty', instead it makes (or should make) any field value, and even the existence of such a value, undefined, inaccessible and irrelevant. That is what I meant before when I wrote before that NULL is a state: just stop thinking of NULL as a value!
Post #976121
Posted Thursday, August 26, 2010 11:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 36,769, Visits: 31,225
Alex-668179 (8/26/2010)
AFAIK, NULL is the only way to leave a field empty!


I disagree: setting a field to NULL does not 'leave [or make] a field empty', instead it makes (or should make) any field value, and even the existence of such a value, undefined, inaccessible and irrelevant. That is what I meant before when I wrote before that NULL is a state: just stop thinking of NULL as a value!


Z'actly.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #976153
Posted Friday, August 27, 2010 12:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 5,926, Visits: 8,175
frodriguez.im (8/26/2010)
what is an empty value? AFAIK, NULL is the only way to leave a field empty!

For string columns and variables, the empty string is an often-used synonym for the zero-length string: ''.
I guess a varbinary could also be considered empty when the contents are zero-length. Other data types do not support an empty value, as there are no empty values in the various numeric domains, nor in the date, time, or datetime domains. (Maybe xml does support some kind of empty value, though I think you can only do that with untyped xml - but I am far from an expert in the field of xml, so I might be wrong).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #976180
Posted Wednesday, September 1, 2010 4:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:46 PM
Points: 32, Visits: 85

This next table summarizes the effect of NULLs in AND expressions:

AND True False NULL
True True False NULL
False False False False
NULL NULL False NULL




This second table summarizes the effect of NULLs in OR expressions:

OR True False NULL
True True True True
False True False NULL
NULL True NULL NULL



I tested both of these truth tables for the underlined conditions:

false AND null

and

true OR null

because I wasn't sure about the behavior here. The programmer in me said short circuiting might work, while the ternary logician in me misremembered a NULL result in both of those conditions. On both my 2k8 and 2k5 installations, I only got the OR behavior to match the article. Is there a setting I'm missing? I tried this with ansi_nulls both off and on with the same result.


using this SQL:

if(1=1 or 1=null)
print 'test passed'
else
print 'test didn''t pass'

if(1=0 and 1=null)
print 'test passed'
else
print 'test didn''t pass'



My output is:

test passed
test didn't pass

Post #979209
Posted Thursday, September 2, 2010 12:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 5,926, Visits: 8,175
oscar.leeper (9/1/2010)
On both my 2k8 and 2k5 installations, I only got the OR behavior to match the article. Is there a setting I'm missing?

No, you are making a logic error. Your test does not distinguish between Unknown (sorry for being pedantic, but the third value in three-valued logic is NOT NULL, but Unknown). According tho the truth tables, the AND test should return False. And it does, but you'd get the same output if it did return Unknown.

Try it with this code:
if(1=1 or 1=null)
print 'test passed'
else
if not(1=1 or 1=null)
print 'test didn''t pass'
else
print 'test result unknown'

if(1=0 and 1=null)
print 'test passed'
if not(1=0 and 1=null)
print 'test didn''t pass'
else
print 'test result unknown'




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #979309
Posted Thursday, September 2, 2010 3:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
I try to avoid NULLs, as far as is practicable, in my designs. They complicate coding and often result in suboptimal query performance.

In suitable cases, I have no problem with so-called 'magic values', if they make logical sense. For example, I accept the idea of using a value like '9999-12-31' for an end date rather than using NULL.

One thing I did not like in the article was the repeated use of constructions like WHERE ISNULL(column, magic_value) <> test_value. Whether you try to handle NULL's inconvenient behaviour with a CASE statement (or the equivalent COALESCE or NULLIF expressions) or ISNULL, the result is the same: a non-SARGable expression.

Using an explicit OR seems preferable to me - and you at least give the optimizer a fighting chance at finding an efficient plan. In many cases, rewriting the query as a UNION of the non-NULL and NULL conditions works better still.

Anyone who has ever had to write a (correct) query to determine if a NULLable column has changed surely shares my dislike for the things. Sometimes they are unavoidable, but that doesn't mean it's not worth the attempt.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #979375
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse