Are There Reasons for Not Storing Boolean Values in SQL as Bit Data Types

Are there reasons for not storing boolean values in SQL as bit data types?

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.

What is the best data type to store boolean values in a database

you can use TINYINT or bit datatype

Which MySQL data type to use for storing boolean values

For MySQL 5.0.3 and higher, you can use BIT. The manual says:

As of MySQL 5.0.3, the BIT data type is used to store bit-field
values. A type of BIT(M) enables storage of M-bit values. M can range
from 1 to 64.

Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which are at the moment aliases of tinyint(1):

Bool, Boolean: These types are synonyms for TINYINT(1). A value of
zero is considered false. Non-zero
values are considered true.

MySQL also states that:

We intend to implement full boolean
type handling, in accordance with
standard SQL, in a future MySQL
release.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

Boolean 'NOT' in T-SQL not working on 'bit' datatype?

Use the ~ operator:

DECLARE @MyBoolean bit
SET @MyBoolean = 0
SET @MyBoolean = ~@MyBoolean
SELECT @MyBoolean

BOOLEAN or TINYINT confusion

MySQL does not have internal boolean data type. It uses the smallest integer data type - TINYINT.

The BOOLEAN and BOOL are equivalents of TINYINT(1), because they are synonyms.

Try to create this table -

CREATE TABLE table1 (
  column1 BOOLEAN DEFAULT NULL
);

Then run SHOW CREATE TABLE, you will get this output -

CREATE TABLE `table1` (
  `column1` tinyint(1) DEFAULT NULL
)

Why are BOOLEAN type columns problematic in relational database design?

Tom Kyte pretty much echoes your last sentence in this blog entry:

"It just isn't a type we have -- I can say no more and no less. ANSI
doesn't have it -- many databases don't have it (we are certainly not
alone). In the grand scheme of things -- I would say the
priotization of this is pretty "low" (thats my opinion there)."

He's speaking from the Oracle perspective, but it applies to any relational RDBMS.

---

Related Topics