boolean programming

Is bit the closest to the boolean (true or false) datatype in tsql? Even
though it could be 0,1,null'
TIABIT is a numeric datatype. There is no assignable boolean datatype in SQL
Server - booleans are only valid for expressions.
If BIT doesn't suit then use a CHAR:
... b CHAR(1) NOT NULL CHECK (c IN ('T','F'))
David Portas
SQL Server MVP
--|||Not really, for a column which can have only two values, the best approach
in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
One could consider the Bit type to be closer to numeric datatypes like INT
or TINYINT since they have common values and several operators overlapping
among them.
Anith|||Hello Anith,
why would a CHAR(1) be the best approach?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:%23y0z1V5kFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Not really, for a column which can have only two values, the best approach
> in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
> One could consider the Bit type to be closer to numeric datatypes like INT
> or TINYINT since they have common values and several operators overlapping
> among them.
> --
> Anith
>|||> why would a CHAR(1) be the best approach?
Well, I'm not sure of the definition of "best"... but some benefits of
CHAR(1) are:
- you can store T/F or Y/N instead of 0/1.
- you eliminate confusion for VB/Access people, who often expect -1 to mean
true, and this does not work with BIT.
- you can easier implement indexes, group by, etc.
Drawbacks:
- it is no longer language-neutral (if this is an issue) since French would
expect v for vrai, or o for oui. Same goes for several other languages.
- it is subject to the same problems as BIT as far as NULLability goes.
- you lose the potential ability to save space in tables where multiple such
columns exist. Up to 8 BIT columns can share a single byte, whereas
TINYINT/CHAR(1) will always take 1 byte each.|||> Is bit the closest to the boolean (true or false) datatype in tsql? Even
> though it could be 0,1,null'
It really isn't all that difficult to use a BIT column with two-valued logic
as opposed to three-valued logic.
CREATE TABLE dbo.Example
(
TrueFalse BIT NOT NULL
)
Null problem solved, no?|||I actually disagree. I, when looking at boolean datatypes, tend to use
the bit datatype. By making it a NOT NULL column and setting a default
to 0, it should solve your problems. I know for a fact that vb/vb.net
interprets a bit value as boolean. I would recommend explicitly
casting the value to boolean, but it will work.|||The other drawback is that most tools now deal with bit nicely as a boolean
and the char approach requires the UI programmer to handle things
differently than they expect. And too often if the UI developer has to go
out of their way to do something it starts to look like we db folks are
being difficult "again" :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||Thanks Aaron for your explanation. I understand your point that one would
loose the potential ability to save space in tables but wouldn't bit columns
contradict using indexes? How is your feeling on bilcolumns in case of
performance and usability? And what would be an ideal approach to implement
a bit column?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
Newsbeitrag news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||> wouldn't bit columns contradict using indexes?
Yes, indexes on bit columns are seldom useful, and I have never needed one,
but a lot of people complain that Enterprise Manager won't let you do it
(see http://www.aspfaq.com/2530).

> How is your feeling on bilcolumns in case of performance and usability?
I don't have any problems with them, though there are some minor details you
should be aware of. Again, see http://www.aspfaq.com/2530

> And what would be an ideal approach to implement a bit column?
I don't understand how you would change the approach? You either use BIT or
you don't?