Are there reasons for not storing boolean values in SQL as bit data types without NULL? I see them often stored as integers without constraints to limit values to 0 and 1, and as strings with things like T/F, True/False, yes/no, etc., again without constraints. Isn't it better to store them as bits and not have to worry about additional constraints? What am I missing here?
A:
one reason is that people don't know about bit or think that y/n is simpler for formatting. other reason is that sometimes you think: hmm maybe over time this will be more than a bool field. and you make it int just in case.
you're not missing anything :)
Mladen Prajdic
2009-04-22 13:48:53
+13
A:
I'd always stick with the smallest data type I can to store this.
- SQLServer: BIT
- Oracle: NUMBER(1) (or BOOLEAN in PL/SQL)
- MySQL: TINYINT (iirc BOOLEAN maps to this automatically)
Edit: Oracle's BOOLEAN is PL/SQL only, not table definition. Updated answer to reflect this.
R. Bemrose
2009-04-22 13:49:54
Re. "smallest data type available": SQL Server BIT values are not actually one bit wide, but multiples of a full byte wide. However, more than one BIT in the same table is collapsed into the same storage.
Tomalak
2009-04-22 14:24:27
MySQL also supports the BIT type, and its storage requirements work exactly as Tomalak explained.
Chad Birch
2009-04-22 18:54:12
@Chad: MySQL BIT columns are distinctly different from SQLServer BIT columns; a SQLServer BIT field stores just one value, and the BIT fields are combined together when stored if there are more than one. A MySQL BIT field is a bitmask with 1 to 64 bits (based on the declaration).
R. Bemrose
2009-04-22 19:07:55
The significance being that I can use SQLServer BIT fields easily in WHERE clauses, whereas I'd have to do bit-shifting in MySQL if more than one value is in a BIT column. Unfortunately, MySQL's BOOLEAN type doesn't map out like SQLServer's BIT field either, instead being substituted with a TINYINT. MySQL does recognize TRUE as 1 and FALSE as 0, though.
R. Bemrose
2009-04-22 19:11:12
+5
A:
what typically happens down the road is that someone wants to add also a maybe to yes and no, if you have a bit then now you have to change all your code to tinyint
if you had tinyint to begin with then you don't.....believe me this happens more than you think
SQLMenace
2009-04-22 13:50:11
+3
A:
When I want booleans in the database I always use the bit data type. In SQL they can be NULL. But when running your program you'll have to consider that a bool (e.g. in C#) is a value type which in this case can't be NULL. You'll have to compare with the System.DBNull value.
Rodrigo Guerreiro
2009-04-22 13:50:19
Couldn't you just designate the variable as Nullable<bool>, would that handle the NULL value?
mattruma
2009-04-22 13:52:00
make it a NOT NULL in SQL.......create table bla (answer bit not null)
SQLMenace
2009-04-22 13:53:34
the problem is what ADO.NET supports or some other data access framework. if it allows Nullable<bool> we're ok!
Rodrigo Guerreiro
2009-04-22 14:26:01
+3
A:
We always store the data as a bit, it's small, and more importantly this is the case it is designed for.
We have had times where the end user was going to be working with the data directly, and to them, Yes/No or Y/N was more readable. In this case, we just created a view that reflected the friendlier data display.
mattruma
2009-04-22 13:51:00
+4
A:
I see them often stored as integers without constraints to limit values to 0 and 1, and as strings with things like T/F, True/False, yes/no, etc., again without constraints. Isn't it better to store them as bits and not have to worry about additional constraints?
Yes!
What am I missing here?
Actually it should be "what am I NOT missing here?" and the answer would be: common sense.
TheTXI
2009-04-22 13:51:11
+1
A:
BIT is the datatype normally used to store BOOLEAN values. Simply because if the BIT is 1 then its true and 0 then its false. It is that simple.
TStamper
2009-04-22 13:51:41
A:
I think third normalization form would state that you should have a table that stores the values True and False, and reference that. Make sure you do that with your dates as well!
But who completely adheres to 3NF anyway? ;)
Darren Kopp
2009-04-22 14:07:09
You might want to find a different source for your definition of Third Normal Form. There is nothing that states that all values in all columns must also be stored in tables somewhere.
Tom H.
2009-04-22 15:02:04
A:
I use bit a lot. But sometimes I want to be able to have the ability to return false - or many values of true (like error messaging). So if I use an int instead of boolean I can doe something like:
0 = False 1 = Password incorrect 2 = Username does not exist. 3 = Account locked out - to many failed attempts. 4 = Account disabled.
And so on.
Jim Evans
2009-04-22 14:33:23
Then that's not a boolean. It's a code value. A boolean by definition is true or false and there are not multiple definitions of true.
Tom H.
2009-04-22 15:01:05
As I said I use bit a lot. I then gave an alternative solution. And if you read my post I said sometimes I use an int instead of a boolean.
Jim Evans
2009-04-23 15:39:45
A:
Some reasons not to do so include:
Not all databases have a bit datatype so you use int instead to be able to use differnt backends
In some databases you cannot index a bit field.
And often what you have is not truly a true/false, yes/no with no other possibilities. For instance you might have a bit field for status meaning something like open or closed. But later you realize you need cancelled as a status as well.
HLGEM
2009-04-22 18:51:34