SQL Performance Tuning

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:

• "String of Bits" Data Types columns

  • While IBM does not provide either a BINARY or VARBINARY data type for bit or binary string data, the DBMS provides the same functionality through the FOR BIT DATA attribute. A column defined as CHAR(8) FOR BIT DATA is equivalent to a column defined as BINARY(8), and a column defined as VARCHAR(8) FOR BIT DATA is equivalent to a column defined as VARBINARY(8).

  • Informix's CHAR data type is really a fixed-length "string of bits" type. If you want collations and dictionary sorts, you have to use Informix's NCHAR.

  • Ingres, Microsoft, Oracle, and Sybase all have a binary string data type. Ingres uses the terms BYTE and BYTE VARYING, Microsoft and Sybase call them BINARY and VARBINARY, and Oracle has a RAW data type ( fixed-size only).

• "Logical" Data Types columns

  • Informix's BOOLEAN doesn't provide full SQL Standard BOOLEAN support, although it does allow you to store three values: 0/1/NULL .

  • The Microsoft/Sybase BIT data type is for logical data but allows only two values 0/1 to be inserted. The SQL Standard logical data type, BOOLEAN, requires support for three values: true/false/unknown .

  • While MySQL does have a BIT data type, it is only a synonym for CHAR(1)a fixed-size character string one character long. This provides no support for bit strings, nor does it provide true support for logical data.

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.