Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

tsql

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?

like image 362
anopres Avatar asked Apr 22 '09 13:04

anopres


People also ask

Can you store boolean in SQL?

SQL Server BooleanThere is no boolean data type in SQL Server. However, a common option is to use the BIT data type. A BIT data type is used to store bit values from 1 to 64. So, a BIT field can be used for booleans, providing 1 for TRUE and 0 for FALSE.

Can a bit be used to hold a boolean value?

However, there is a data type called bit that can be used to store Boolean values.

Does SQL support BOOLEAN data type?

There is boolean data type in SQL Server. Its values can be TRUE , FALSE or UNKNOWN . However, the boolean data type is only the result of a boolean expression containing some combination of comparison operators (e.g. = , <> , < , >= ) or logical operators (e.g. AND , OR , IN , EXISTS ).

How many bits does it use to store the boolean value true and false?

Boolean variables are stored as 16-bit (2-byte) numbers, but they can only be True or False. Boolean variables display as either: True or False (when Print is used), or. #TRUE# or #FALSE# (when Write # is used).


1 Answers

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.

like image 65
Powerlord Avatar answered Sep 25 '22 18:09

Powerlord