|
Displaying Source Code(s)
|
|
Why should I avoid NULLs in my database?
--------------------------------------------------------------------------------
Joe Celko said it best: "NULLs confuse people..." (SQL For
Smarties, ISBN 1558605762). McGoveran and Date add:<BR>NULLs...are
far more trouble than they are worth and should be avoided; they
display very strange and inconsistent
behavior and can be a rich source of error and confusion."
(Guide to Sybase and SQL Server, ISBN 020155710X).
My sentiments exactly. Of course, I don't expect to convince you
by flashing a few quotes from very reputable
authors in front of you. Let's talk for a minute about what
exactly NULLs do that cause this type of reaction. The first
problem is that the definition of NULL is "unknown." So, one
problem is determining whether one value is (not)
equal to another value, when one or both values are NULL. This
trickles down to many problems for a database
engine and any associated applications. The following list
details some of those problems:
@li they are interpreted differently depending on compatibility
level and ANSI settings;
For example, let's consider two values, x and y, that are both
NULL. Since the definition of NULL is
unknown, then you can't say x = y. However, with the ANSI
setting ANSI_NULLs, this can be
different. When this setting is FALSE, x = y ... however, when
TRUE, x <> y. Confusing, no?
@li the storage engine has to do extra processing for each row
to determine if the NULLable column is in fact
NULL -- this extra bit field affects storage and indexing, and
obviously has performance implications for
general queries;
@li they produce weird results when using calculations,
comparisons, sorting and grouping;
@li they create problems with aggregates and joins, such as
different answers for COUNT(*) vs.
COUNT(fieldname);
@li they produce unpredictable results in statistics
computations, particularly WITH ROLLUP and WITH CUBE;
@li applications must add extra logic to handle inserting and
retrieving results, which may or may not include
NULL values;
@li they cause unpredictable results with NOT EXISTS and NOT IN
subqueries (working backwards, SQL
determines that NULL columns belong or do not belong to the
result set, usually for the wrong reasons);
@li no language that supports embedded SQL has native support
for NULL SQL values.
-------------------------------------------------------------------------------- |
|
|
123
Eng