Re: Convert bit feld to Yes/No

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ron Hinds (__NoSpam_at___NoSpamramac.com)
Date: 06/07/04 

Date: Mon, 7 Jun 2004 16:45:44 -0700

Hmm... OK here goes. SQL Server defined the bit datatype, not I. As it *is*
a high-level language I really don't know or care how it is stored. All I
need is a consistent result - I store a True value I get back a True value.
I store a False value I get back a False value. If I'm using a Boolean data
type then there is no NULL, a thing simply IS or ISN'T. I always use a
constraint to default to the False value, and to not allow NULLs in that
column.

I do know that the True value of a Boolean datatype can take several forms.
But True is always NOT False, which (at least in my experience) is always
represented as 0 (unless we're talking about hardware that uses Negative
logic - but we won't get into that). In other words, if it is not 0 then it
is True. BTW *all* computer systems work on this principle at the most basic
level (even calculators that *seem* to be using decimal).

Finally, have you never inherited a project that you didn't design? For
which the employer isn't willing to pay for a complete redesign/re-write?
What do you do - simply walk away from it?

"Joe Celko" <jcelko212@earthlink.net> wrote in message
news:%230ULXPcSEHA.1544@TK2MSFTNGP09.phx.gbl...
> >> Keeping the lft and rgt synchronized in a dynamic
> organization might be a little tricky. <<
>
> My work is never done!
>
> Your whole model of SQL and RDBMS is totally wrong. (Hey, that is mild
> abuse from me; I'll try to do better the next time:))
>
> SQL is a high level language; it is abstract and defined without regard
> to PHYSICAL implementation. Bits and Bytes are the **lowest** units of
> hardware-specific, physical implementation you can get. Are you on a
> high-end or low-end machine? Does the machine have 8, 16, 32, 64, or
> 128 bit words? Twos complement or ones complement math? Hey, the
> standards allow decimal machines,so bits do not exist at all!! What
> about NULLs? To be a datatype, you have to have NULLs, so what is a
> NULL bit? By definition a bit, is on or off and has no NULL.
>
> What does the implementation of the host languages do with bits? Did
> you know that +1, 0 and -1 are used for BOOLEANs, but not consistently?
> That means ALL the host languages - present, future and not-yet-defined.
> Surely, no good programmer would ever write non-portable code by getting
> to such a low level as bit fiddling!!
>
> And it scares users who think in terms of 'yes' and 'no', zero and one
> (or is it minus one?).
>
> What some Newbies, who are still thinking in terms of 2GL and 3GL
> programming or even punch cards, do is build a vector for a series of
> "yes/no" status codes, failing to see the status as a single attribute.
> Imagine you have six components for a loan approval, so you allocate
> bits in your 2GL model of the world. You have 64 possible vectors, but
> only 5 of them are valid (you cannot be rejected for bankruptcy and have
> good credit). For your data integrity, you can:
>
> 1) Ignore the problem. This is actually what **most** newbies do. I
> then come in and charge their employer (opps, I meant "former employer")
> big bucks to do a data quality audit :)
>
> 2) Write elaborate CHECK() constraints with UDFs that cannot port and
> that run like cold glue.
>
> Now we add a 7-th condition to the vector -- which end does it go on?
> Why? How did you get it in the right place on all the possible hardware
> that it will ever use? Did all the code that references a bit in a word
> by its position do it right after the change?
>
> Programs are employed by the company longer than the people who wrote
> them -- remember that little "Y2K" thing?
>
> There are VERY good reasons that we have First Normal Form (1NF).
>
> You need to sit down and think about how to design an encoding of the
> data that is high level, general enough to expand, abstract and
> portable. For example, is that loan approval a hierarchical code?
> concatentation code? vector code? etc? Did you provide codes for
> unknown, missing and N/A values? It is not easy to design such things!!
>
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Bitwise OR just like SUM or COUNT
    ... SQL is a high level language; ... SQL Server's BIT datatype ...
    (microsoft.public.sqlserver.programming)
  • Re: Progamming language for hackers?
    ... >But I think I understand this programming language! ... I like to "aaa the (datatype) bbb and I'd like to have ... How to do (the main verb) - part ...
    (comp.programming)
  • Re: SQL Injection - SQL query comments
    ... SELECT statements joined by UNION must have the same number of columns with compatible datatypes in each statement. ... You'll have to cast USERNAME as a sql_variant datatype - it wouldn't hurt to cast the filler columns as well just so you don't have to figure out the ... SQL Injection - SQL query comments ... Cross site scripting and other web attacks before hackers do! ...
    (Pen-Test)
  • Re: Simple Best Practice Question
    ... , @Param1 datatype ... Beginning SQL Programming (Programmer to Programmer), John Kauffman, Brian ... The book gives step-by-step instructions, alerts readers to common pitfalls, ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Data Types (7 vs 2k)
    ... What it sounds like is they have a WHERE clause that is ... comparing two different datatypes and the proper indexes are being used. ... Andrew J. Kelly SQL MVP ... > datatype precedence in WHERE clauses in SQL2K vs. ...
    (microsoft.public.sqlserver.programming)