Re: Strange Wierdness with SQLServer Bit fields and SQLProvider

Inline.

On 11/27/07, Marc Brooks <[email protected]> wrote:
>
> I suspect you're not talking to the database you think you are...
> perhaps working with a cached connection string to some other server?


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.


But, onto the rest of the post...
> > For this example ref_isvalid defines if we're in debug mode, ( there are
> > 200+ ref tables like this , so changing NOT NULL is not possible.)


Ummm, sure it is... given your schema, this will actually take less
> room (since a single bit in a row will be allocated to an INT and
> waste the rest of the bits)


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.

>
> > create procedure SP_GetState(@debug bit) as
> > begin
> >   select * from ref_state where ref_state = 1 or @debug = 1
> > end
>
> Eeew, surely you are actually NOT using SELECT * (right?)  Does anyone
> actually care about those audit columns you add down the road?  Never
> use *.  Never.

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

2.  Select Field1, field2, field3 .... fieldn  from table    isn't always
the fastest way to get data, if you have a huge number of fields and you are
returning all the fields , or close to all the fields from the datatable 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. Depends on the system,
Oracle 8+ and  SQLServer 6.5+  , have this little problem, it was quite
demonstrable on my last contract, for a merchantile bank, 16 million records
of 289 fields per row. Select *  over Select F1, f2,  etc shaved a couple of
seconds off of the calculation time.

> when we loop over the dataset ,  we use this function
>
>  public T DefaultValue<T>(object o , object defaultValue){
>  if(o == null || o == DBNull.Value) return defaultValue;
>  return (T)o;
>  }

>>Seems like you should really check if "o" is an instance of DBNull
>>before blindly comparing it's value against DBNull.Value, right?  So

Can't see the problem with comparing the reference class with the class
instance of DBNull.Value,
If I was comparing   o  == 3    then sure, I would test for an integer
for o,  but DBNull.Value is an instance reference and not a value object ,
or that was what I always belived.  I'll take a look again.

>  refItem.IsValid = DefaultValue<Boolean>(dr["ref_isValid"],false);

When, exactly, would you pass anything other than false to such a read
(of the REF_* tables).
// typed in google, so syntactically pants.
  string lastName = DefaultValue<String>(dr["user_surname"],string.Empty);
  Guid  userId = DefaultValue<Guid>(dr["user_uid"],Guid.NewGuid());

>>Marc

>>p.s. NULL in databases is evil.... it just makes things harder and
>>forces you to scatter your defaulting logic all over.  Think in terms
>>of the NullObject pattern... where a specific row/value encodes the
>>_behavior_ of what you think NULL would mean.  It's more explicit and
>>easier to follow... For example:

Completely disagree here, how would you handle Foreign Keys that have no
child data?
add an extra magic number into the table to suggest that it doesn't have a
child reference?

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.

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?

   1           25.35     Sent 25/12/2007    2           -1     Awaiting
confirmation client
   3           100        Paid 01/01/2008


   Select Sum(price) as Total from sales
   Total = 124.35     --- Clearly wrong

   so we have to do
   Select Sum(price) as Total from sales where price <> -1

   Ledger systems will normally put Purchases as negative figures and Sales
as positive figures
   So we can't use -1 How about 0 nothing in life is ever free right?

;-P

Davy,

===================================
This list is hosted by DevelopMentor®  http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com