Re: Strange Wierdness with SQLServer Bit fields and SQLProvider

> Ok, Mark, when serious discussions degenerate to sarcasm and fluff, I would
> usually pull off.

Umm, it's Marc.  I wasn't sarcastic in the slightest, sorry that you
felt that I was. As for fluff, you can discard my opinion without
insult it, or not... I don't care.

> You are willing to replace null with a "flag" without mentioning
> the data type of the flag. What is the memory layout of that datatype?

In the example I gave (which you glossed over), I specifically called
it out as a TINYINT, which is a single signed byte in SQL Server.  I
believe that SQL Server uses twos-complement on disk, not that I would
care much... that said I ALSO pointed out in the orginal example that
this was actually a space saving as having just one NULLable field
causes SQL Server to allocate a bitmask. In this case, it would be at
least 15 wasted bits (probably 31 wasted bits for SQL Server 2000)
which means either 1 or 3 wasted bytes PER ROW.  That's on top of the
actual BIT value (which again is allocated against a bitmask) for
another waste of either 1 or 3 bytes PER ROW. Overall, my enumeration
takes 1 byte, the BIT plus nullability takes 2 + 2 (best case) or 4 +
4 (worst case) bytes.  That's a significant waste per row.

> How many bits does it take to represent every member of that set?

In the specific example given, there were three states, so that's a
two-bit value.  As with most things, it depends on what the LOGICAL
design calls for... in his case, I doubt the NULL was actually
meaningful for the REF_STATE table, especially given that he could
have DEFAULTed the value to the business-desired value.

> By the way, for your education, NaN mean (Not a Number). Nuff said!

Yes, and that's why it's a pain in the ass to deal with... I guarantee
I know more about the IEEE float format and how to deal with it than
you do... had to emulate it in C++ on embedded processors way back
when... more recently I've built (and corrected) .Net classes to deal
with the oddities of NaN... I know what it means, that's WHY I pointed
out that it can bite you just like NULL does.

> I saw your data model and I did see the obvious defaulting of the guid() to
> all 0 bit. I am sure you think, "yeah! it's a guid type, therefore it's a
> uniquely constrained column!". I am yet to see the unique constraint.

You missed it, and you missed it again when I called it out for you.
The OwnerID is _indeed_ defaulted to Guid.Empty... but the ID column
(which is a UNIQUEIDENTIFIER ROWGUIDID column is defaulted to NEWID().
 Check again:

CREATE TABLE [dbo].[Profile](
       [ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT
[DF_Profile_ID]  DEFAULT (newid()),
       [Type] [char](1) NOT NULL CONSTRAINT [DF_Profile_Type]  DEFAULT ('U'),
       [UserName] [nvarchar](150) NOT NULL,
       [Password] [nvarchar](50) NOT NULL,
       [FriendlyName] [nvarchar](50) NOT NULL CONSTRAINT
[DF_Profile_FriendlyName]  DEFAULT (''),
       [OwnerId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Profile_OwnerId]
 DEFAULT ('00000000-0000-0000-0000-000000000000'),
       [ValidFrom] [datetime] NOT NULL CONSTRAINT [DF_Profile_ValidFrom]
DEFAULT (getutcdate()),
       [ValidTo] [datetime] NOT NULL CONSTRAINT [DF_Profile_ValidTo]
DEFAULT ('9999-12-31 23:59:59.997'),
 CONSTRAINT [PK_Profile] PRIMARY KEY CLUSTERED
(
       [ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

> Your disdain about higher education tells me much more about you and why
> your reasoning is so off about the science of computer programming

Ummm, my disdain is not for higher education, it's for people that
think a college degree implies knowlege of a high level on some
practice-based subject.

> you to lean only on symptomatology as the sure way to analyze what is and is
> not good about software engineering.

I've seen bridges, skywalks and building collapse that have been
designed and built by college graduates.  Those failures kill people.
My first "real" job was writing software for medical laboratories.
Failures in those systems kill people. I take correctness and hard
specification of behavior VERY seriously.

> The most pitiful of that symptomatology
> is in the example you quite unprofessionally called "sexy" ...

Oops, you're right.. I was sacastic in that comment... I stand corrected.

> want to optimize memory and relocate undefined space (eg: cache hit
> optimization algorithm), what would you do?

As someone who has written a chess program for a 1.1K Kim-1 computer
(where memory did count at a byte level) and as someone who right a 2
million line PL/I application that ran in the DOS small model (e.g.
64K code, 64K data) [albiet with extensive overlaying needed], I
suggest that I know how to optimize for memory constraints.  This
wasn't a discussion about null pointers until YOU made it so... I was
talking about SQL NULL.

> How do you distinguish between a location in memory that is unstable
> and one initialized by an empty string

.Net String is a reference type. It's already setup to handle null vs.
empty.  That said, most of my code doesn't ever allow null strings.
The exception is on public interface boundaries where passing a null
indicates NO CHANGE (not NO VALUE).

> because that is the business value and yet again the one with your magic
> value of <empty string>?

Let me ask you this... in what way, in a real design such I as I used,
not an academic discussion, did you answer that question?  Show me
some place where you consistently gave business meaning to a database
string that was nullable that wouldn't have been better served by
either a validity-flag (validity-enum) and value OR was not
consistently defaulted to an empty string in the business layer.  Show
me one REAL project design where you coded some other useful
meaningful interpretation of what NULL in a VARCHAR or such column
meant something... then we can discuss an engineering design decision
instead of philosophy.

I already did this, by the way...

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

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