Re: Strange Wierdness with SQLServer Bit fields and SQLProvider

> Admittedly, null may act weird sometime.

NULL acts wierd ALL the time... how else can you talk about something
that is simultaneously equal AND not-equal to itself (see NaN in IEEE
float formats for another edge-case waiting to bite you).

> If you loose null, you loose lots of things that null enables in advanced
> designing.

<singing> Anything NULL can do, a valid-flag can do clearer </singing>

Seriously, NULL is considered anathema in SQL databases by one of the
INVENTORS of SQL.  Chris Date (of Date & Cobb) fame considers NULL in
databases to be "inherently flawed".  The other guy (E.F. Date), who
supports NULL considers the current NULL to be "completely inadequate"
and suggest that you need TWO NULLs to indicate whether something is
"missing but applicable" or "missing but NOT applicable".  The fact
that the two guys that started the whole mess don't agree that it
should be there, and that the one that says it should argues that it's
not right as-is tells me something. [0]

So, you want a special value (NULL) that must be logically interpreted
every where it is used instead of a flag that consistently indicates
the true meaning... I'll have to agree to disagree with that.

> A practical application is that Null let's you have optional foreign/unique
> keys.

So every query, every application needs to handle the what-if the
foreign-key value is missing?  So every INNER JOIN gets turned into a
LEFT JOIN and thus all the other columns in that joined-to table must
be special-cased to handle the missing related record... joy.

> Null actually makes unique keys practical. You cannot have a default value
> on a unique constraint.

Huh?  I can't do what I DID on the example I posted?  That means I
have metric shit-tonne of applications to fix... I wonder how I'm
going to go through all those applications and fix the data that
couldn't be defaulted.... I guess that call to NewID() just seemed to
work.  Shucks.

> Some people (and am one of them) even think that null is elegant.

I think infinity is elegant too... but I don't find much application
to it in my database designs.

> So much so that Microsoft had to yield to the practical pressure to bubble
> nullity into the  .NET  valueType space

They bent to the will of people that wanted to deal with database
nulls, but not the elegence of design... which is why we now have to
litter our code with lovely constructs like:

string ownerName = record.owner.HasValue() ? record.owner.name : string.Empty;

Sexy!!!

> And by the way, I think the NullObject pattern was created to help prevent
> people from throwing NullReferenceException in the application layer.

Then you would be wrong.  The NullObject pattern exists to centralize
the logical behavior when a value is not-otherwise-set. I used it in
PL/I code in the early 80s to avoid having hundreds of places in code
to check for a null object and special case the code... and there was
no SQL, .Net or NullReferenceException in sight. The NullObject
pattern was first called that (as far as I know) by Martin Fowler,
whose design skills I have personally benefitted from in several
projects. You wouldn't believe the simplifications it allows when
writing code... no if statements, no ternary magic, not even the cool
coalescing operation need... just reference your NullObject and play
along. [1]

> not intended for storing crazy stuff in the database, otherwise, all types
> may need to be reduced to some magic value. Magic values are a poor
> replacement for 'nothingness', probably because the magic value in the first
> place is not a type of 'nothingness'.

As indeed is NULL.  It's an out-of-band special value, to be sure, but
it IS still a special value, and is a poor substitute for _why_ the
value is not there... an enumeration is far my useful for this...

> I still prefer to test before you 'dot' and that works for the goose.quark()
> as well as gander->quark() because, let's face it, gander.quark() is
> undefined and could be left as such until we approach the UI.

You would rather remember to check every time... you would rather pay
the execution speed for this check every time... you would rather
special case the behavior every time... in every UI (be it the web
site, the thick client, the XML web service, the SQL report, and the
myriad Crystal reports).  Me, I would rather establish the reasonable
convention one place... but I'm lazy and care about execution speed.

> These are personal opinions,
> however, and am sure that there are cuter opinions out there. The cutest of
> it all for me is that null unifies everything for all types, meaning one
> thing and only one thing: no value is appropriate because in there's no
> place for this thing in the space of all possible members of the alphabet of
> this set.

Cute isn't the issue, nor it NULL unifying. In fact, it's exactly the
opposite.  You cannot say two NULLs are the same... they aren't.  NULL
represents discontinuity more than unity.  Your dismissal of the use
of it is a clear indicator of someone that hasn't _actually used it_.
 Much like those that dismiss TDD as too much work don't have the
practice to see where it saves so much.

> And anytime you set a magic value with the magical powers of indicating a
> loss of existence.

I never advocated a magic number.  I said that the NullObject pattern
should be used when appropriate, and that you shoud use a flag or
enumeration when it is important to capture WHY something is
missing/not-used/not-valid... this is something NULL can't do. Ever.

> localization people,

As someone who has developed hundreds of localized programs in CP/M,
Dos, Windows, HTML, etc... I can tell you that having to scatter round
your code the check for null-substitution is MUCH harder than putting
them in one place.

> No doubt, the null enigma needs some taming ...

Part of that taming is in understand where it is broken.  Part of that
is understanding the true implications of your design's decisions.

Guess how many null-checks my social-network site's code needs?  None.
 Any idea how much faster that is? I do, I've benchmarked it.

Guess how many places I have to code the behavior of a unowned profile? One.

Guess how many places I have to enable localization of that entity?
Two (gotcha!) one in the code and one in the view that is used for
reporting (and PLEASE tell me that you don't code reports against the
actual table.... please?!?)

> Well, then, maybe that's why it takes a  college degree before people get
> unleashed to go trying these kinds of stuff on their own.

If only computer science was like that... hell, that wouldn't help...
one of the smartest coders I know, with a CS Masters, didn't know
until instructed by me why clustered indexes were good, why UPDATE
statements can READ data, and why you don't put BIT values in
indexes... college degrees mean you paid the tution and (maybe)
attended some classes...

Marc

p.s. I notice you didn't comment on my schema example... I suspect you
didn't even read it... since you didn't see the OBVIOUS defaulting of
the ID column...

[0] http://en.wikipedia.org/wiki/Null_(SQL)
[1] http://en.wikipedia.org/wiki/Null_object_pattern

-- 
"He uses statistics as a drunken man uses lamp-posts… for support
rather than illumination." Andrew Lang

Marc C. Brooks
http://musingmarc.blogspot.com