FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

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
NULLs
> 
> and the three-value logic they entail were a bad thing, and frankly
that
> was after I had come to see them as a big annoyance since they don't
work 
> 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
is nullable:

        K-A-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
something useful.  
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
and
        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
as
        WHERE A = coalesce(B,A)

On SQL Server, I've found that sort of thing to be much faster than OR
clauses.  YMMV.  

> Both you and Joe Geiser refer to problems the use of empty strings
instead 
> of NULLs causes.  It may be my lack of experience in RDBMs, but it's
not yet 
> clear to me exactly what the nature of those problems is.  In fact,
others 
> have told me the same thing about NULLs, namely that they break
relational 
> models and will reach back and bite you at unexpected times.  I do
know that 
> 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,
simple  
> examples of the problems caused by the use of empty strings instead of

> NULLs?

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)

        B
        ----------------------




ISQL would show "NULL" for nulls, but mere blank space for zero-length
strings.  

If it's  a CHAR field, you can't tell how many spaces are in the
non-data:

1> create table #t(t char(1) not null)
2> insert #t values ('')
3> go
(1 row affected)
1> select * from #t
2> go
 t
 -


(1 row affected)
1> select * from #t where t = '   '
2> go
 t
 -


(1 row affected)
1>

Consider also:

        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
code:

        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.  

Regards, 

--jkl

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