Tinyint vs Bit

Question

I do not want to touch the religious war here, but there seem to be two schools of thought on how to represent logical values ​​in a database. Some say bit is the appropriate data type, while others claim tinyint better.

The only differences that I know of are the following:

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

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

Answer 1

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

Tinyint and bit can be created to work, I used both options and do not have strong preferences.

Answer 2

Bit ... if you are not from the clan "true / false / file not found"

If you have not received the link ...

And in the case of Linq2SQL, the bit works with true / false, which simplifies programming. There are benefits for both.

And there also should be considered programming. What happens if you (or a junior trainee programmer) use 2, 3, 25, 41, 167, 200, etc.? Where is this documented? Bits are self-documenting and fairly universal.

Answer 3

I use bits when necessary. In addition to the semantic correct type (semantics of count!), Several bit fields (up to 8) in one line (on SQL Server, anyway) can be combined into one byte of storage. After the eighth, an extra byte is required for the next 8, etc.

Literature:

• SQL 2000
• SQL 2005
• SQL 2008

Answer 4

For MySql users - Why you shouldn't use BIT columns in MySQL .

Answer 5

Previous StackOverflow post: What is the difference between BIT and TINYINT in MySQL?

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

I would just stick to BOOL (aka TINYINT ) and continue my life.

Answer 6

I use bit because it eliminates the need for me to use a check constraint, and because my ORM automatically converts the bit to a null boolean (C #) value, which I really appreciate after coding.

Answer 7

All these theoretical discussions are great, but in fact, at least if you are using MySQL and really for SQLServer, it is best to stick with non-binary data for your logical elements for the simple reason that it is easier to work when you output data, queries, etc. d. This is especially important if you are trying to achieve interoperability between MySQL and SQLServer (i.e., synchronize data between them), since processing two types of BIT data is different from two. So in practice you will have much less problems if you stick to a numerical data type. I would recommend MySQL use BOOL or BOOLEAN, which is stored as TINYINT (1). Even how MySQL Workbench and the MySQL administrator display the BIT data type are not nice (it's a small character for binary data). So be practical and save yourself from the hustle and bustle (and, unfortunately, I'm speaking from experience).

Answer 8

Logical, by definition, allows only two values. Why do you need something more than one bit for this? if you need a third (or more) state logic, then use a larger data type, but I (and am) sticking to bit fields for standard logic.

Answer 9

I was just trying to group a bit (SQL Server 2k5) and it worked fine for me. I like to use the correct data type for the application. If this field is true / false, then the bit is what I use ...

Answer 10

I do not think I saw this above, but the problem is that you cannot fill in the BIT columns (e.g. MIN, MAX and especially SUM). I just tested 2008 and the problem is still there. This is the biggest reason I have been using tinyint lately - another, I like how tinyint scales are always a pain when your two-digit bit bit suddenly needs more possible values.

Answer 11

Zero Space for False

Whatever your choice, you can set NULL instead of 0 , and it won’t take up too much space (since the database almost always has a NULL flag for each field of each row, it just sits there; more information here ). If you also make sure that the default / most likely value is false , you will save even more space!

Some space for True

The value for representing true requires the space defined by the field type; using BIT will only save space if the table has several such columns, since it uses one byte per 8 fields (compared to TINYINT , which uses one byte per field).

TINYINT has the advantage that you can set the 8-value bitmask without worrying about managing many additional columns, and the search is theoretically faster (one whole field compared to several bit fields). But there are some flaws, such as slow ordering, fancy cross-indexing material, and lack of field names. Which for me is the biggest loss; Your database will need external documentation to note which bits did what the bitmasks did.

In any case, avoid the temptation to use TEXT fields to store logical or sets of them. Searching through text is a lot more work for the server, and arbitrary naming schemes such as on, off, off can damage interoperability.

Answer 12

We build all our tables with the vector field. Then we use this field as a set of 32 bits that we can assign for any purpose. (Potentially using a group of bits for a set of states). Avoids that we need to add flag fields if we forget.

Answer 13

@Kevin: I believe you can use group by in 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 14

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

Answer 15

I like to use char (1) with 'T' or 'F'. Yes, it can be abused with other values, but at least it can be easily viewed in reports or in other places where it is more difficult to work with bit or binary values.