[freetds] OT: NULL Considered Helpful
Lowden, James K
LowdenJK at bernstein.com
Tue Aug 10 16:08:34 EDT 2004
From: Charles Bearden
Sent: Tuesday, August 10, 2004 12:19 PM
> On Mon, 9 Aug 2004 James K. Lowden wrote:
> > Respectfully submitted, I think using zero-length strings to
> > circumvent non-null column policy isn't the best practice. You're
> > really saying there's no data for that column (in this row), so NULL
> > accurately reflects reality. Zero-length strings are very
> > mysterious in ad-hoc queries, because it's hard to see nothing. And
> > some versions of TDS can't distinguish between NULL and a
> > zero-length string, meaning that some clients will see a NULL
> > arriving from a (as declared) non-null column. So, fwiw, I think
> > you're better off making your column nullable. And you get to avoid
> > the bug in the bargain. :-)
> Thanks for your response.
> I'm not sure what to think. I had been pretty well persuaded that
> and the three-value logic they entail were a bad thing, and frankly
> was after I had come to see them as a big annoyance since they don't
> with parameter placeholders in WHERE clauses.
The knock on NULL has a good theoretical foundation, and I'm not going
to challenge it. Every relational guru will tell you they don't belong
in the model or in your design, except when they do. The trouble with
an absolutist never-say-null policy is that no existing RDBMS will
tolerate a "correctly" designed database i.e., one sufficiently
normalized to eliminate NULLs.
A nullable column is an attribute that has an optional relationship to
its key. That is, its relationship to the key is different from those
of the non-nullable columns. According to normalization rules, such
attributes constitute a separate entity, and thus need their own table.
Suppose you have a table with key K and attributes A and B, of which B
The fact is that there will be more A's than B's. What you really have,
normalizationwise, is two tables:
K-A -+--o+- K-B
so the K-A entity has an optional relationship to K-B.
The problem is that no extant technology holds up very well if you
dutifully break out all your optional relations into their own tables.
Especially if K is very big, the redundant storage and joining effort --
combined with the fact that most nullable columns have their own
distinct cardinality and thus each needs its own table -- quickly
explode "simple" 4-table joins into 12-table or more, and overwhelm the
server. In short: it's right, but not efficient. So we compromise.
Having compromised, some folks are tempted to adhere to a no-null policy
by subverting the data. (You can tell from that characterization what I
think about that.) The introduce special data to represent what's
really NULL: -1 for the unknown foreign ID, say, or 'June 6, 2079' for
an unknown date. Or 'N/A' or '' for an unknown string. IMO, you're
better off with NULL because:
1. With coalesce() and isnull(), it's easier to convert an unknown to
2. NULL is "standard" whereas every database designer can come up with
his own pseudo-data. Over time, the number and kind of representations
for (what would be) NULL tends to grow, and grow incompatibly.
3. It doesn't help.
Why doesn't it help? In isolated, special-use cases, it might, but in
general any query that has to cope with NULL would have to cope with the
special case, too. Consider:
WHERE A = B or B is NULL
WHERE A = B or B = ''
Don't think so? How about:
WHERE B between 'Apple' and 'Camel'
If B is anything besides real, honest-to-god data, you're going to need
an OR clause.
Illustrating argument #1, we could express
WHERE A = B or B is NULL
WHERE A = coalesce(B,A)
On SQL Server, I've found that sort of thing to be much faster than OR
> Both you and Joe Geiser refer to problems the use of empty strings
> of NULLs causes. It may be my lack of experience in RDBMs, but it's
> clear to me exactly what the nature of those problems is. In fact,
> have told me the same thing about NULLs, namely that they break
> models and will reach back and bite you at unexpected times. I do
> I find NULLs inconvenient because I can't test for them with the same
> operators as I use to test for non-NULL values. Are there some good,
> examples of the problems caused by the use of empty strings instead of
They mostly involve gotchas.
One is "bcp -c". There's no way for a delimited ASCII file to
distinguish between a zero-length string and a NULL. So you basically
can't bulk-load such a table from external sources. If you dump the
table to a flat file with bcp, you won't be able to reload it without
using a second table and some SQL.
Another is that ad hoc queries can be very confusing. For example:
SELECT B FROM T2 WHERE A NOT IN
(SELECT A FROM T1)
ISQL would show "NULL" for nulls, but mere blank space for zero-length
If it's a CHAR field, you can't tell how many spaces are in the
1> create table #t(t char(1) not null)
2> insert #t values ('')
(1 row affected)
1> select * from #t
(1 row affected)
1> select * from #t where t = ' '
(1 row affected)
WHERE B < 'Camel'
picks up all the zero-length strings (and none of the NULLs), which
probably isn't what you want, so you have to write special case ugly
WHERE B < 'Camel' and B <> ''
Oy. The wages of using data to represent nondata.
That's my case, FWIW. I normally don't burden the list with SQL
disquistions, but you did ask, and it wouldn't be nice to leave you
wondering about vague dark allusions to the evils of zero-length
strings. Hope this helps.
The information contained in this transmission may contain privileged and confidential information and is intended only for the use of the person(s) named above. If you are not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender immediately by reply e-mail and destroy all copies of the original message. Please note that we do not accept account orders and/or instructions by e-mail, and therefore will not be responsible for carrying out such orders and/or instructions.
If you, as the intended recipient of this message, the purpose of which is to inform and update our clients, prospects and consultants of developments relating to our services and products, would not like to receive further e-mail correspondence from the sender, please "reply" to the sender indicating your wishes. In the U.S.: 1345 Avenue of the Americas, New York, NY 10105.
More information about the FreeTDS