Re: Strange Wierdness with SQLServer Bit fields and SQLProvider

> As i said there's test cluster been put into the system, The dev boxes using
> NT authentication were hitting the correct database, the ASP.NET as beeing
> "load balanced" and hitting a second database attached to the project, one
> without the modifications, the DBA has moved his tests off to a couple of
> other machines after some insistance on my part , and won't be a problem
> again.

Glad to hear it... I suspected something like this was up based on
your description of the issues... that's why I stated that first :)


> Nope can't change the existing table structure this project is already in
> production and being used, we cannot make that much of a major change.

You _can_ make the change, you _choose_ not to... I respect that.  I
also wanted to point out a way that you could do this that steered you
from the dangers of a NULL BIT value in SQL.  As I stated, NULLs bite
you in odd ways... your concern that there was something odd going on
shows the level of logical discomfort they sometime can cause.

> Normally I would agree, selecting the specific fields required would be best
> practice however.
>
> 1. This database is in production and being used by other systems apart from
> our front end, using * even with the performance impact, allows us to
> publish upgrades / patches without stoping 20+ inconnected systems

Just so you understand... asking for columns you don't need can
sometimes cause things that would have been a simple index scan to
turn into a scan+bookmark lookup.  Additionally, if you are not doing
column-name-based access in the .Net side, (e.g. using the column
index), you can get hosed if someone inserts a column (not appends,
inserts).

> it is actually faster to return the fields in a random order with * and let
> the client sort out what it wants from the results.

True, some older databases have issues with long select lists... but
it's usually ACTUALLY the statement parse/send time that causes
that... I've benchmarked it a lot.  In modern DB2, Oracle and SQL
Server, you aren't going to see that... and if you are dealing with
TPS rates like that, you probably should be using stored procedures
just to reduce the network overhead.

> Can't see the problem with comparing the reference class with the class
> instance of DBNull.Value,

You are correct... just seemed a bit wierd to me.  DBNull is a
reference-type object.

> Completely disagree here, how would you handle Foreign Keys that have no
> child data?

Let me rephrase your query... how do YOU handle FK references that are
NULL?  Where do you code the "if NULL" logic?  How many places would
that logic have to be?

> add an extra magic number into the table to suggest that it doesn't have a
> child reference?

No.  Not a magic number, for FK's a reference to the NullObject.  For
value-type columns, you use sentinal values or nothing at all.  Most
of the times I've seen people use non-FK NULL columns, they've been
dates, which are definately better served by sentinal values since
then the value can participate in indexes and consistently use BETWEEN
clauses.

> When you return the raw data from the table, you have worse problems as you
> have to make sure the magic number is the same in the database as in the
> code.

Indeed, and for those cases, you should probably have a state/status
enumeration or not merge things into on table that should have been in
two (or more)... most non-FK NULL columns are signs of incomplete
normalization.

> example:
>
> Sales:
>   orderId , price , comment
>   1           25.35     Sent 25/12/2007
>   2           null     Awaiting confirmation client
>   3           100        Paid 01/01/2008
>
>
>  Select Sum(price) as Total from sales
>
>  Total = 125.35
>
> If we do as you suggest,  we use -1  for price  because nobody sells
> anything at -1$ correct?

Sales
 OrderId   Price  Status           Comment
 1          25.35 Shipped           2007-12-25
 2          45.67   QuotePending  Client confirmation
 3          100.00 Paid               2009-01-01

SELECT COUNT(*), SUM(Price) FROM Sales WHERE Status >= Shipped
2    125.35

SELECT COUNT(*), SUM(Price) FROM Sales WHERE Status < Shipped
1    45.67

Assuming you've got an enumeration for Status which increases as the
order goes through the processes... or perhaps seperate dates is more
your style:

Sales
 OrderId   Price  Quoted       Shipped        Paid          Comment
 1          25.35   2007-10-11  2007-12-25   9999-12-31
 2          45.67   9999-12-31  9999-12-31   9999-12-31  Pending
client confirmation
 3          100.00 2007-11-12  2007-11-14   2009-01-01

SELECT COUNT(*), SUM(Price) FROM Sales WHERE Shipped < GetUtcDate()
2    125.35

SELECT COUNT(*), SUM(Price) FROM Sales WHERE Shipped > GetUtcDate()
1    45.67

SELECT COUNT(*), SUM(Price) FROM Sales WHERE DateDiff(dd, Shipped, Paid) > 30
1    100.00

Far more utility that allowing a NULL for a column, in my opinion...

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

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