Mark,

I am looking for exactly the kind of info/advise you just provided.
That is the first concrete reason I have received as to why this might
be a valid idea.

What is your opinion on using 0 as the default for numeric fields?

As far as I am concerned, 0 is a number in its own right, and there are
bound to be situations where we will want to indicate that there is no
value stored by using null. In a situation where 0 is a valid value,
how do you differentiate between the default value of 0 and a user
inputted value of 0?

The reason I am putting these questions out there is that I have never
come across the idea of defaulting every column on every table in a
database, and was wondering if anyone else has come across this
concept, or even better, worked with an implementation where this was
used.

Thanks,
John.

DB2 has NULL as well as DEFAULT compression. AFAIK that does NOT require
the compression feature. So the benefit of NOT NULL columns may be less
than one might think.

Cheers
Serge

DaFrizller:

FWIW, I agree strongly w/ Knut: this is not merely a storage issue but
a design issue. As you may be aware, the whole NULL/2VL/3VL issue is
very prickly, and pros and cons for both allowing and disallowing NULLs
abound (flip over to comp.databases.theory if you'd like to read some
spittle-flecked rants on the subject). The issue is not merely bytes
but also of semantics.

OK--enough soapboxing. You asked about Hibernate. One issue I'm aware
of is that it's not enough to declare a DEFAULT in DB2; you must also
declare the (same) default value in the BO (.java file). If you do not,
you risk overwriting the database-written default with whatever value
is in the instance variable mapped to the database field (often NULL),
when the Java values are persisted.

--Jeff

Maybe you have not worked as a DBA in a large application development
project, but the design of the database to support the business
requirements is usually defined by the data modeler for the logical
model and the DBA for the physical design (usually these are the same
person). Databases that are designed by developers (usually by
committee) are often times not very good in my experience (even though
I am a former developer).

I did not give any "arguments" in favor of not using nulls, I just said
that using nulls requires an extra byte for each nullable column (I
don't know about any native compression). However, I would find it hard
to believe that these extra bytes would be a real issue unless there is
an extremely large data warehouse being designed. But as noted, if the
requirements are to distinguish a null from a zero, then I would think
using nulls is justified in almost all cases.

That should have been "1 byte per nullable value"