SQL Pocket Guide, 2nd Edition

Datatypes

Database systems support far more datatypes than this small book can cover adequately. This section describes some commonly used types that will meet many of your needs. Consult your vendor documentation if you need to store data that cannot be accommodated by the following types. See "Tables, Creating,” later in the text, for examples of datatype usage.

Character String Types

For all platforms except Oracle, use the VARCHAR type to store character data:

VARCHAR(max_bytes)

MySQL allows TEXT as a synonym for VARCHAR:

TEXT (max_bytes)

In Oracle, append a 2 to get VARCHAR2:

VARCHAR2(max_bytes)

Oracle Database 9i and higher allow you to specify explicitly whether the size refers to bytes or characters:

VARCHAR2(max_bytes BYTE)
VARCHAR2(max_characters CHAR)

Using Oracle’s CHAR option means that all indexing into the string (such as with SUBSTR) is performed in terms of characters, not bytes. For more on Oracle’s CHAR option, which is actually quite important, see http://gennick.com/char.html.

Maximums are: 4,000 bytes (Oracle), 32,672 bytes (DB2), 8,000 bytes (SQL Server), 65,532 bytes (MySQL), and 10,485,760 bytes (PostgreSQL).

Decimal Types

All platforms support the use of DECIMAL for storing numeric, base-10 data (such as monetary amounts):

DECIMAL
DECIMAL(precision)
DECIMAL(precision, scale)

In Oracle, DECIMAL is a synonym for NUMBER, and you should generally use NUMBER instead.

DECIMAL(precision) is a decimal integer of up to precision digits. DECIMAL(precision, scale) is a fixed-point ...