The SQL Standard provides two data types for columns that contain bit strings: BIT and BIT VARYING. It also provides one data type for columns that contain "logical" bits: BOOLEAN. Table 7-11 shows the SQL Standard requirements and the level of support (data type and maximum size in bytes) the Big Eight have for these data types.
Table 7-11. ANSI/DBMS Bit Support
The word "bit" can mean different things depending on what DBMS you look at, so we've divided the BIT data types into two categories. In Table 7-11, the "string of bits" data types are the SQL Standard BIT and BIT VARYING (with a length argument equal to the fixed and maximum number of bits, respectively) plus the nonstandard SQL extensions BINARY and VARBINARY (with a length argument equal to the fixed and maximum number of bytes, respectively). The values in each of these data types should be just a bunch of zeros and onesraw data that has no meaning to the system. Sometimes people store character data in "string of bits" columns to evade the character set or collation rules associated with a character string data type.
The "logical" data types shown in Table 7-11 are the SQL Standard BOOLEAN and the nonstandard SQL extension BIT. The values in logical columns represent true/false/unknown conditions. It is unfortunate that Microsoft and Sybase call this data type BIT because it is unrelated to the true SQL Standard BIT data type. The correct word really is BOOLEAN.
Notes on Table 7-11:
The Bottom Line: Bits
BIT and BIT VARYING are unsupported data types, at least in the SQL Standard style.
BINARY and VARBINARY are handy retainers for data too small to be BLOBs, and too meaningless to be CHARs but are nonstandard SQL extensions. Most DBMSs provide support for binary strings under one name or another.
Recommendation: If portability is a major concern, store bit and binary strings in BLOBs. Use the DBMS's nonstandard binary string data type if space is at a premium.
The BOOLEAN data type is also unsupported in the SQL Standard style. The DBMSs that have a BOOLEAN (Informix), BOOL (MySQL), or BIT (Microsoft and Sybase) data type don't provide true Boolean support.
Recommendation: Take a leaf from MySQL's book. The BOOLEAN data type can be replaced with CHAR(1), which takes the same amount of space (one byte) and requires no conversion effort when used with an API. Add a CHECK constraint to limit the acceptable data, and you'll have betterand more portableBoolean support than is otherwise possible. BOOLEANs will be worth something only when they can operate according to the SQL Standard, which isn't the case now.