Re: Bitwise OR just like SUM or COUNT

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 11:37:24 +0200

On Wed, 20 Oct 2004 18:31:02 -0700, Joe Celko wrote:

(snip)
>Crappy machine level things like a BIT or BYTE datatype have no place in
>a high level language like SQL. SQL is a high level language; it is
>abstract and defined without regard to PHYSICAL implementation. This
>basic principle of data modeling is called data abstraction.

Hi Joe,

I agree with the byte. But a BIT can be defined at the logical level as a
value that is either 0 or 1. This definition is independent of physical
implementation.

Don't forget that SQL-92 includes a BIT string. SQL Server's BIT datatype
is equal to SQL-92's BIT(1). SQL Server's BINARY and VARBINARY datatypes
are roughly equal to SQL-92's BIT and BIT VARYING datatypes.

>Bits and Bytes are the <i>lowest<i> 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!

I don't care. Neither do I care how dates, strings, integers or floats are
stored on a particular machine. As long as I can enter a specific value
for a BIT column and see the same value when I retrieve it, I couldn't
care less - as far as I'm concerned, the implementation may even choose to
translate the bit values 0 and 1 to character strings 'off' and 'on' and
store them like that, as long as they are translated back to 0 and 1 upon
retrieval.

> What about NULLs? To be a SQL datatype, you
>have to have NULLs, so what is a NULL bit? By definition a bit, is on
>or off and has no NULL.

That would rule out integers as datatype as well. What is a NULL integer?
By definition, an integer is a whole postivie number, a whole negative
number or zero and has no NULL.

Oh wait! An integer datatype in SQL is defined as either an integer
according to the mathematical definition, or NULL. Why not extend this
trick to define BIT as either a bit value (i.e. 0 or 1), or NULL?

Again - check the SQL-92 standard.

> If you vendor adds NULLs to bit, how are the
>bit-wise operations defined?

I'd hope that they are defined just as numeric, string and datetime
operations arre defined: if one of the operands is NULL, the result is
NULL.

Of course, the SQL-92 standard only includes concatenation and substring
as valid operators for bit strings; bitwise operators are not included.
They would be an extension of the standard. But the same goes for many
other operations (ever try to find the modulo operation in the SQL-92
standard?), so that shouldn't be a problem.

> Oh what a tangled web we weave when first
>we mix logical and physical :)

Unless we stop and take some time to think about it first...

>What does the implementation of the host languages do with bits?

I don't care. Do you? Do you care what the implementation of host
languages does with integers? datetimes? etc.?

> Did
>you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
>consistently?

Who's talking about BOOLEANs? I thought this message was about BITs?

Of course, if you start to confuse BIT values with BOOLEAN values in the
host language, you'll be in a mess. Exactly the same sort of mess you'd
get when you confuse CHAR(1) values that are either 'T' or 'F' with
BOOLEAN values.

>There are usually two situations in practice.
(snip)
>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?
>concatenation code? vector code? etc? Did you provide codes for
>unknown, missing and N/A values? It is not easy to design such things!

I can agree heartily with the whole remainder of your message. Designs
involving bits are ALMOST always better replaced by a more logical and
more human-understandable redesign. But as I don't know everything there
is to know, I can't remove the word ALMOST from the same sentence.

The column name "status" in the original poster's message suggests that
this is indeed an example of bad design. But I can't tell for sure without
knowing much more about the OP's business problem.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Convert bit feld to Yes/No
    ... SQL Server defined the bit datatype, ... a high-level language I really don't know or care how it is stored. ... I do know that the True value of a Boolean datatype can take several forms. ... > SQL is a high level language; it is abstract and defined without regard ...
    (microsoft.public.sqlserver.programming)
  • Re: To bit or not to bit...
    ... SQL is a high level language; ... abstract and defined without regard to PHYSICAL implementation. ... datatype, you have to have NULLs, so what is a NULL bit? ...
    (microsoft.public.sqlserver.programming)
  • Re: Update 1 row at a time?
    ... > BIT or BYTE datatype have no place in a high level language like SQL. ...
    (microsoft.public.sqlserver.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: 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)