I think NULL is the logical choice too, but I would also say that "knowing what NULL means" is pretty context-dependent! Take an employment termination date in a personnel record. While the employee is still employed, the termination date should (I suppose) be null, but that doesn't mean it's unknown, it means that because it hasn't yet happened yet it doesn't exist at all! Perhaps this is too much of a straw-splitting distinction.

Sorting NULL fields can be a pain though. Often you want them to sort before any other date, as SQL Server does by default, but other times (as in the case of the termination date above) you want it to sort after. Hence the unwelcome appearance of magic values (one system I use uses a date in the year 2500 for this sort of thing).

What might be cool would be to take a leaf from PostgreSQL's book and introduce two new values: positive infinity (i.e not NULL but it sorts after everything else) and negative infinity (not NULL but sorts before everything else). I've never actually used PostgreSQL so can't speak from experience, but it seems like a good idea. Some purists would no doubt disagree and I respect that. I have no interest in starting a religious war.

I think that the root of the problem is that when you have several concepts (unknown, doesn't exst, not defined, ...) which are easily confused, context dependent and generally slippery to work with, trying to use just one thing (i.e. NULL) for all of them is necessarily going to involve a few compromises. And some extra work.

MarkD