Boolean Datatype
Kenneth Soh L M
As there is no such thing as Boolean data type in SQLServer,
is char(1) the best option ?
Please comment and cc: ken...@pacific.net.sg.
Thank you very much.
Dan Guzman
which affects temp tables in stored procedures with BIT fields.
Hope this helps.
Kenneth Soh L M wrote in message <01bdb3c9$f685bc80$d5010196@kenpc>...
TAddison
Scott David Daniels
1) They cannot be NULL.
2) You cannot use them in a GROUP BY (nor expressions derived from them)
3) and they go really nutso on outer joins because they cannot be, but
are, NULL.
I would go so far as to say, "Don't use bit for booleans".
-Scott David Daniels
Scott....@Acm.Org
Tim Elley wrote:
> This got me thinking - what if you want a 3 state boolean field? Would
> you use a char(1) field or perhaps a tinyint field. My inclination would
> be a tinyint, and maybe using a User Defined Datatype (though I haven't
> played with them before), though I have no reason or substance behind
> that preference.
>
> Anyone any thoughts on that?
Tim Elley
Francis Stanisci
true(1) or false(0).
I've never understood there to be any other state. If you need another
"state" this is no longer
boolean logic. This sounds almost like "creative accounting".
If someone can show my logic flawed please feel free to correct me. :-)
Francis
Tim Elley
the sort of functionality that can be obtained with an MS Access boolean
field. I have occasionally found it usefull where I need to record
values of True, False and NA (Not Applicable).
The alternative is either something like my earlier idea, or to have a
second boolean field that controls whether or not another boolean field
is applicable. I must say that I'm not keen on this particular work
around.
> Unless I'm missing something here, if your using "boolean" logic it's either
> true(1) or false(0).
> I've never understood there to be any other state. If you need another
> "state" this is no longer
> boolean logic. This sounds almost like "creative accounting".
>
> If someone can show my logic flawed please feel free to correct me. :-)
>
Francis Stanisci
down the path others have gone with the English language. We have enough trouble
on our hands as it is without introducing new "interpretations" of words,
phrases, etc. Let's NOT bastardize the SQL language, just as has been done with
the English language.
Call things what they are, not what you want them to be.
Boolean is (yes,no), (true, false), (on,off), (0,1), period. Anything else is
NOT boolean, so don't refer to it as if it was. You'll only confuse others and
propogate bad logic add infinitem! This is a technical language, not an artistic
language. It is "rigid" for a very good reason, reliability.
I'm done venting, thank you.
Francis
Scott David Daniels wrote:
> Good idea. Using bit is frought with problems:
> 1) They cannot be NULL.
> 2) You cannot use them in a GROUP BY (nor expressions derived from them)
> 3) and they go really nutso on outer joins because they cannot be, but
> are, NULL.
> I would go so far as to say, "Don't use bit for booleans".
>
> -Scott David Daniels
> Scott....@Acm.Org
>
> Tim Elley wrote:
>
> > This got me thinking - what if you want a 3 state boolean field? Would
> > you use a char(1) field or perhaps a tinyint field. My inclination would
> > be a tinyint, and maybe using a User Defined Datatype (though I haven't
> > played with them before), though I have no reason or substance behind
> > that preference.
> >
Scott David Daniels
a boolean that MSS doesn't have is that a bollean value behaves much like "a<b".
The SQL standard agrees. That is "select foo from tbl where 5 >= int_field" and
"select foo from tbl where bool_field" are parallel structures. By this
measure,
MSS does not have booleans.
As to "multi-state" booleans, again the example above may explain what makes
sense in the twisted world of SQL/database logic. This next assumes field is a
NULLable int.
tbl.field tbl.field > 3 tbl.field <= 3 NOT tbl.field >
3
2 false true
true
7 true false
false
null null null
null
Francis Stanisci wrote:
> Unless I'm missing something here, if your using "boolean" logic it's either
> true(1) or false(0). I've never understood there to be any other state. If
> you
> need another "state" this is no longer boolean logic. This sounds almost like
> "creative accounting".
By the bye, there is no particular reason 0 = false except that you've read too
many C programs. Various languages have chosen various values for those two.
Now why is this foolishness about NULL important?
In the expression:
SELECT base.some, tbl.field, tbl.field > 3 AS cond
FROM (base LEFT OUTER JOIN tbl ON base .some = tbl.field) AS x
WHERE base.some IN (2,3,5,7)
a base table of:
some
2
5
7
Should give a result of:
some field cond
2 2 false
5 null null
7 7 true
Even worse, suppose we add another field "boolo" to tbl, and set all values to
true
We should get:
SELECT base.some, tbl.field, tbl.boolo, tbl.field > 3
FROM (base LEFT OUTER JOIN tbl ON base .some = tbl.field) AS x
WHERE base.some IN (2,3,5,7)
some field boolo cond
2 2 true false
5 null null null
7 7 true true
In this case whether or not "boolo" was NULLable in tbl, it is going to have to
be NULLable in this expression. If we cannot deal with NULL fields then we will
always have to worry about values in expressions that we cannot predict. If we
can have them, but cannot store them in tables, we are even worse off, since we
are stuck with having queries that work as long as the optimizer does not write
any intermediate tables. This means that a program with a query imbedded in it
may break if the next version of the query optimizer makes different choices
about what tables are stored as temporaries.
Sorry about the long-windedness, I just have heard the argument a tad too often,
and thought it worth writing down an answer this time.
-Scott David Daniels
Scott....@Acm.Org
Francis Stanisci
Boolean: A True/False value. It can be True or False in Visual Basic. It can be TRUE
or FALSE in C++
DboLogin: Boolean, Indicates if the login used for the connection (contained in the
SQLServer object) has DBO privilege in this database. (i.e. a login is or isn't DBO,
period, my comments).
I don't know or understand where you get the information to support your logic.
You mention "SQL standard", please where did you get that from?
I've always seen a boolean as a bit field, never an INT, SMALLINT, CHAR, etc.
If you do a search on "boolean" in BOL you will find every example (that I looked
at) with true or false and nothing else.
Please get your facts write, take a look at any math book/manual and you will find
the same. SQL was designed around the mathamatical models of E.F. Codd at IBM in
1972. So please get your facts straight.
Thank you.
Francis