SQL data types

Choosing column types, and the ones that cause trouble later.

What you'll be able to do

Choose appropriate column types
Explain why money should not be FLOAT
Know the CHAR/VARCHAR distinction

The common types

VARCHAR(n)   -- variable-length text
CHAR(n)      -- fixed-length, padded with spaces
TEXT         -- unbounded text
INT          -- whole numbers
BIGINT       -- large whole numbers
DECIMAL(p,s) -- exact decimal, e.g. DECIMAL(10,2)
FLOAT/REAL   -- approximate floating point
BOOLEAN      -- true/false
DATE         -- date only
TIMESTAMP    -- date and time

Never use FLOAT for money

Floating point cannot represent many decimal fractions exactly:

0.1 + 0.2 = 0.30000000000000004

Over thousands of rows those errors accumulate and totals stop reconciling. Use DECIMAL(10,2), which stores the value exactly. FLOAT is for measurements where tiny imprecision is acceptable.

CHAR versus VARCHAR

CHAR(10) always occupies ten characters, padding with spaces. That padding is why a CHAR column compared to a VARCHAR value can fail to match, which is a genuinely confusing bug.

Use VARCHAR unless the value is genuinely fixed-length, like a two-letter state code.

Dates as text

Storing dates in a VARCHAR is a common inherited problem. It sorts alphabetically, breaks date arithmetic, and prevents index-assisted range scans. Convert on import.

Key points

Use DECIMAL for money, never FLOAT
CHAR pads with spaces, which can break comparisons
Dates stored as text sort alphabetically and break arithmetic

Check yourself