Alex Rivera | Logout

Tinyint vs Bit?

Asked 2009-01-28T18:43:01.210
92

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?

sql mysql sql-server types
Edit
Report

3 Answers

103

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.

answered 2009-01-28T18:55:06.367
3

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.

answered 2009-01-28T18:45:52.080
1

I don't think I saw it mentioned above, but there's the issue of not being able to aggregate BIT columns (e.g. MIN, MAX, and especially SUM). I just tested using 2008 and the issue is still there. That's the biggest reason I use tinyint lately - the other being I like how tinyint scales - it's always a pain when your "two-value" bit flag suddenly needs more possible values.

answered 2011-03-26T20:12:55.113

Your Answer