Tinyint vs Bit

Question

I don't want to touch-off a religious war here, but there seem to be two schools of thoughts in how to represent boolean values in a database. Some say bit is the appropriate data type, while others argue tinyint is better.

The only differences I'm aware of are these:

• bit: storage size is 1 bit, possible values are 0 or 1
• tinyint: storage size is 1 byte, possible values are 0-255

Which data type is better when you need to represent boolean values? Is tinyint worth the extra overhead "just in case" you need to values > 1?

Answer 1

Bit...unless you're of the "true / false / file not found" clan

In case you didn't get the reference...

And in the case of Linq2SQL, bit works with true/false which makes it easier to program for. There's advantages to both.

And there's also programming maintenance to consider. What happens if you (or a junior intern programmer) uses a 2, 3, 25, 41, 167, 200 etc? Where is that documented? Bits are self-documenting and pretty universal.

Answer 2

Boolean, by definition, allows only two values. Why would you need anything more than a single bit for this? if you need a three (or more) state logic, then use a bigger datatype, but I would (and do) stick with bit fields for standard boolean logic.

Answer 3

I just tried grouping on bit (SQL Server 2k5) and it worked fine for me. I like using the correct data type for the application. If it's a true/false field, then bit is what i use...

Answer 4

We build all our tables with an int "vector" field. We then use that field as a collection of 32 bits that we can assign for any purpose. (Potentially using a group of bits for a set of states). Avoids us having to keep adding in flag fields if we forget.

Answer 5

@Kevin: I believe you can use group by on bit fields (SQL Server 2005):

declare @t table (
    descr varchar(10),
    myBit1 bit, 
    myBit2 bit
)
insert into @t values ('test1', 0, 1)
insert into @t values ('test2', 1, 0)
insert into @t values ('test3', 1, 1)
insert into @t values ('test4', 0, 0)

select myBit1, count(myBit1) from @t group by myBit1
select myBit2, count(myBit1) from @t group by myBit2

Results:

myBit1 
------ -----------
0      2
1      2

myBit2 
------ -----------
0      2
1      2

Answer 6

A previous StackOverflow post: http://stackoverflow.com/questions/290223/what-is-the-difference-between-bit-and-tinyint-in-mysql

When adding a new "BOOL" column, MySQL actually uses TINYINT.

I'd just stick with BOOL (aka TINYINT) and move on with life.

Answer 7

For MySql users - Why you should not use BIT columns in MySQL

Answer 8

If you're using MySQL, then it's not recommended to use the BIT data type - http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/

Answer 9

I use bits when appropriate. Aside from it being semantically the correct type (semantics count!), multiple bit fields (up to 8) in a single row (on SQL Server, anyway) can be consolidated into a single byte of storage. After the eighth, an additional byte is needed for the next 8, and so on.

References:

• SQL 2000
• SQL 2005
• SQL 2008

Answer 10

When you add a bit column to your table it will occupy a whole byte in each record, not just a single bit. When you add a second bit column it will be stored in the same byte. The ninth bit column will require a second byte of storage. Tables with 1 bit column will not gain any storage benefit.

Tinyint and bit can both be made to work, I have used both successfully and have no strong preference.

Answer 11

I like using char(1) with 'T' or 'F'. Yes it can be abused with other values but at least it is easy to view in reports or other places where bit or binary values are harder to work with.

Answer 12

I use bit because it saves me having to use a check constraint, and because my ORM will automatically convert bit into a nullable boolean (C#), which I very much appreciate once coding.