To Null or not to Null that's my question

[A.M. de Jong]

Reading a lot about Nulls right now I still can't find a Technical reason to
use it or not.

For what I've understand is this:

In an Ingres database a Null column has a standard extra storage of 2 Bits.
In a SQL Server database every column has a NULL-bit telling about this
column it is NULL or NOT.
That means that a varchar-empty NULLABLE column takes no space at all since
the Nullable column defines it as NULL.
In that respect: When a lot of varchar columns tend to be empty from the
space point of view make it NULLABLE.

A varchar not nullable column must be filled with a '' when it's empty. That
single quote takes 3 bits since a varchar has an overhead of 2 positions.

I am not talking about key-values although sometimes you see
reference-columns to a Master table wich is emtpy (NULL in my case)
Since the master table had no NULL-key item there is no Inner join facility.
I don't think SQL likes the outer join that much so it's clear to create an
UNKNOWN reference to the master table.
(And place this Unknown item in the master table as well).

But the rest: When should I use nulls and when do I do not ???

Arno de Jong, The Netherlands.

[Simon Hayes]

"A.M. de Jong" <ar****@wxs.n l> wrote in message
news:bo******** **@reader08.wxs .nl...

Reading a lot about Nulls right now I still can't find a Technical reason to use it or not.

For what I've understand is this:

In an Ingres database a Null column has a standard extra storage of 2 Bits. In a SQL Server database every column has a NULL-bit telling about this
column it is NULL or NOT.
That means that a varchar-empty NULLABLE column takes no space at all since the Nullable column defines it as NULL.
In that respect: When a lot of varchar columns tend to be empty from the
space point of view make it NULLABLE.

A varchar not nullable column must be filled with a '' when it's empty. That single quote takes 3 bits since a varchar has an overhead of 2 positions.

I am not talking about key-values although sometimes you see
reference-columns to a Master table wich is emtpy (NULL in my case)
Since the master table had no NULL-key item there is no Inner join facility. I don't think SQL likes the outer join that much so it's clear to create an UNKNOWN reference to the master table.
(And place this Unknown item in the master table as well).

But the rest: When should I use nulls and when do I do not ???

Arno de Jong, The Netherlands.

Use NULLs when your data model requires it - that's much more important than
the physical storage requirements (at least for most people). In general, if
the column is an optional attribute, then use NULL; if the attribute is
optional but there is a sensible default available, use NOT NULL with a
DEFAULT constraint; if the attribute is not optional, it should be NOT NULL.

For more information, you might want to search for newsgroup postings by Joe
Celko, and his book "SQL for Smarties" discusses NULLability in some detail.

If you have a specific implementation issue related to NULL/NOT NULL,
perhaps you could give more details (including table DDL), and someone may
be able to point you in the right direction.

Simon

[Anith Sen]

If you looking for logical correctness & practicality of using NULLs in
relational databases, then I would recommend you to go through Date's
Relational Database Writing 89-91 & 91-94 for an excellent treatment on the
issue. It is a very crucial & practical yet widely misunderstood concept and
usage of NULLs have been ingrained to our thought process by SQL databases.
Though Simon has suggested Joe Celko's book, I am a bit skeptical of the
suggestions in his book about using them, being biased based on SQL's flawed
approach to n-VL.

However, if you are just concerned with implementing a SQL table with or
without NULLs, with little concern over precise modeling practices, simply
read though the vendor manual. Nulls, irrespective of how often
characterized as markers for unknown/inapplicable values, are treated like a
"value" in most vendor DBMSs.

--
-- Anith
( Please reply to newsgroups only )

[Joe Celko]

Quit worrying about the storage used by various products; they will make
more and chepaer storage for you. The real question is how to use them
in a data model. My heuristics are:

1) Avoid them if you can. Use a dummy value and put it into a DEFAULT
clause, if the domain of your column needs it.

2) If you have a NULL, make usre it has one and only one meaning in that
domain. Example: The ICD codes for disease use 000.000 for
"Undiagnose d" and 999.999 for "Diagnosed, and we don't know what it is"
for two kinds of missing data.

3) Make sure the propagation property of NULLs makes sense in your data
model. Otherwise, use COALESCE() to replace it with another value.

4) Remember that you must use "CAST (NULL AS <datatype>)" in places
where the compiler needs to know the datatype of a column or variable.

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!