Every column you create carries a data type, and that single choice quietly decides how much disk your table burns, which values the database will refuse, and how fast your queries run. Pick well and the engine works for you. Pick carelessly and you inherit rounding bugs, bloated storage, and slow scans. This guide walks the main type families with real examples so you always reach for the right one.
What this guide covers
Why data types matter
A data type is a contract. It tells the database exactly what kind of value a column may hold, and that contract pays off in three concrete ways.
- Storage. A four byte INT and an eight byte BIGINT both hold whole numbers, but across a hundred million rows the difference is nearly 400 MB. The type sets the on disk footprint of every row, every index, and every backup.
- Validation. Declare a column DATE and the engine rejects 'banana' before it can ever poison a report. The type is your first and cheapest layer of data integrity, working alongside DDL constraints like NOT NULL and CHECK.
- Performance. Fixed width numeric types compare and sort faster than strings, they pack more rows per page so fewer pages are read, and they let indexes stay compact. Storing a number as text quietly defeats range scans and forces implicit conversions.
If you are still finding your feet with schemas, the SQL fundamentals primer covers tables and columns before you dive into the type catalogue below.
Numeric types
Numeric types split into two camps: exact types that store a value precisely, and approximate floating point types that trade precision for range. Knowing which camp you are in is the difference between money that always balances and money that drifts by a cent.
| Type | Stores | Example | Notes |
|---|---|---|---|
| TINYINT | Small whole numbers, 1 byte | age TINYINT UNSIGNED | Range 0 to 255 unsigned. Ideal for ages, small counts, status codes. |
| INT | Whole numbers, 4 bytes | quantity INT | Range about +/- 2.1 billion. The default choice for counts and IDs. |
| BIGINT | Large whole numbers, 8 bytes | views BIGINT | Range about +/- 9.2 quintillion. Use when INT could overflow. |
| DECIMAL(p,s) | Exact fixed point numbers | price DECIMAL(10,2) | p total digits, s after the point. Exact: no rounding drift. Use for money. |
| FLOAT / DOUBLE | Approximate real numbers | reading DOUBLE | Fast and wide range, but binary rounding. For science, not cash. |
The classic trap is the difference between DECIMAL and FLOAT. A float cannot represent 0.1 exactly in binary, so a sum of many floats slowly drifts:
-- FLOAT for money: values will not add up cleanly
CREATE TABLE invoice (total FLOAT);
INSERT INTO invoice VALUES (0.1), (0.2);
SELECT SUM(total) FROM invoice; -- 0.30000000000000004-- DECIMAL for money: exact to the cent, every time
CREATE TABLE invoice (total DECIMAL(10,2));
INSERT INTO invoice VALUES (0.1), (0.2);
SELECT SUM(total) FROM invoice; -- 0.30Watch out: never store currency, tax rates, or anything you audit in a FLOAT or DOUBLE. Use DECIMAL with an explicit scale, or store integer minor units (cents) in a BIGINT.
String and text types
Text types differ on one axis that matters most: fixed length versus variable length, and where the value physically lives. The three you will use constantly are CHAR, VARCHAR, and TEXT.
| Type | Stores | Example | Notes |
|---|---|---|---|
| CHAR(n) | Fixed length string, padded | country CHAR(2) | Always n characters, right padded with spaces. Best when length is constant. |
| VARCHAR(n) | Variable length up to n | email VARCHAR(255) | Stores only what you use plus a small length prefix. The everyday default. |
| TEXT | Long, unbounded text | body TEXT | For articles and comments. Often stored off page and cannot always be fully indexed. |
Reach for CHAR only when the value truly is a fixed width, such as an ISO country code (CHAR(2)), a currency code (CHAR(3)), or a fixed length hash. For almost everything else, names, emails, URLs, titles, use VARCHAR(n) with a sensible ceiling. Reserve TEXT for genuinely long free form content where you do not know the maximum size. One more habit worth forming: use a Unicode capable type or collation (utf8mb4 in MySQL, text in PostgreSQL) so names and emoji survive intact.
Date and time types
Temporal types are where portability and time zones cause the most grief. There are four workhorses, and the important distinction is whether a type carries a time zone and how wide its range is.
| Type | Stores | Example | Notes |
|---|---|---|---|
| DATE | Calendar date only | 2026-07-04 | No time component. Birthdays, invoice dates, deadlines. |
| TIME | Time of day only | 09:30:00 | No date. Opening hours, durations in some engines. |
| DATETIME | Date plus time, no zone | 2026-07-04 09:30:00 | Stored exactly as written. Never shifts with the server zone. |
| TIMESTAMP | A point in time, zone aware | 2026-07-04 09:30:00 UTC | In MySQL stored as UTC and converted on read. Narrower range. |
The safest, most portable way to write a date value is the ISO 8601 string 'YYYY-MM-DD', which every engine casts without ambiguity:
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2026-02-01'; -- half open range, index friendlyBoolean types
A boolean holds true or false, and support for it is refreshingly inconsistent. PostgreSQL has a real BOOLEAN type. MySQL accepts the keyword BOOLEAN but treats it as an alias for TINYINT(1), where 1 is true and 0 is false. SQL Server has no boolean at all and you model it with a BIT column.
| Type | Stores | Example | Notes |
|---|---|---|---|
| BOOLEAN (PostgreSQL) | true / false / NULL | is_active BOOLEAN | A first class three valued type. Accepts TRUE, FALSE, and NULL. |
| TINYINT(1) (MySQL) | 0 or 1 | is_active TINYINT(1) | BOOLEAN maps to this. Query with = 1 or = 0. |
| BIT (SQL Server) | 0, 1, or NULL | is_active BIT | The idiomatic flag column on SQL Server. |
Binary, JSON and other types
Beyond the everyday families, modern engines ship types for raw bytes, semi structured documents, and unique identifiers. These are powerful but easy to misuse.
| Type | Stores | Example | Notes |
|---|---|---|---|
| BLOB / BYTEA | Raw binary bytes | Thumbnail, file bytes | Great for small binaries. Prefer object storage plus a URL for large files. |
| JSON / JSONB | Structured documents | '{"tier":"pro"}' | Validated on insert and queryable. PostgreSQL JSONB is indexable. |
| UUID | 128 bit unique identifier | a1b2c3d4-...-e5f6 | Native in PostgreSQL and SQL Server (UNIQUEIDENTIFIER). Store as 16 bytes, not text. |
| ENUM | One value from a fixed list | status ENUM('new','paid') | Compact in MySQL, but a lookup table is often more flexible. |
JSON is not a schema shortcut: a JSON column is perfect for sparse, changing attributes, but resist the urge to dump every field into it. Data you filter, join, or sort on belongs in real typed columns where indexes and constraints can help you.
A CREATE TABLE that uses a good mix
Here is a realistic table that pulls one type from each family, so you can see how the choices fit together in practice:
CREATE TABLE orders (
id BIGINT PRIMARY KEY, -- large id space
order_ref CHAR(10) NOT NULL UNIQUE, -- fixed width code
customer VARCHAR(120) NOT NULL, -- variable text
quantity INT NOT NULL, -- whole count
total_amount DECIMAL(12,2) NOT NULL, -- exact money
is_paid BOOLEAN NOT NULL DEFAULT FALSE, -- flag
metadata JSON, -- sparse extras
ordered_on DATE NOT NULL, -- calendar date
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);Notice how each type is the smallest, most exact one that still fits the data: DECIMAL for cash, DATE where no clock time is needed, and a fixed CHAR(10) for a code that is always ten characters. Keep the SQL syntax guide handy if any of the clause ordering above looks unfamiliar.
Best practice notes that save you later
DECIMAL, not FLOAT, for money
We covered the drift above, so the rule is short: anything you would put on an invoice, a ledger, or a tax return goes in DECIMAL(p,s). Choose s to match the currency (2 for dollars and euros, sometimes 4 for unit prices) and p large enough for the biggest total you will ever sum.
CHAR versus VARCHAR
CHAR(n) always occupies n characters and right pads shorter values with spaces, which can surprise you when comparing or concatenating. VARCHAR(n) stores only the characters you use plus a length prefix. Use CHAR when every value is the same length (codes, hashes), and VARCHAR for everything with variable length. Do not pick CHAR hoping for speed: on modern engines the difference is negligible and correctness matters more.
TIMESTAMP versus DATETIME and time zones
In MySQL the two are genuinely different. DATETIME stores the literal wall clock value you gave it and never shifts. TIMESTAMP converts your value to UTC on write and back to the session time zone on read, so the same row can display differently in Tokyo and New York. TIMESTAMP also has a narrower range (roughly 1970 to 2038 in older setups). The pragmatic rule: for a real moment in time that must be correct worldwide, store UTC (a zone aware timestamp such as PostgreSQL TIMESTAMPTZ or MySQL TIMESTAMP) and convert for display. For a fixed local value like a store opening date, a plain DATETIME or DATE is clearer.
Right size your columns: a VARCHAR(255) for a two letter country code, or a BIGINT where a SMALLINT would do, wastes space in every row and every index and slows scans. Choose the smallest type that comfortably fits real data plus headroom, not the biggest one out of habit. Oversized types are a recurring theme in our common database design mistakes.
Cross engine differences at a glance
The type families are universal, but each engine spells and sizes them a little differently. When you move a schema between systems, this table is the quick reference. For the authoritative detail, consult the official documentation: the MySQL data types reference, the PostgreSQL data types chapter, and the SQL Server data types guide.
| Concept | MySQL | PostgreSQL | SQL Server |
|---|---|---|---|
| Big integer | BIGINT | BIGINT | BIGINT |
| Exact decimal | DECIMAL | NUMERIC | DECIMAL |
| Variable text | VARCHAR | VARCHAR / TEXT | VARCHAR / NVARCHAR |
| Boolean | TINYINT(1) | BOOLEAN | BIT |
| Timestamp with zone | TIMESTAMP | TIMESTAMPTZ | DATETIMEOFFSET |
| JSON | JSON | JSON / JSONB | NVARCHAR + ISJSON |
| UUID | BINARY(16) | UUID | UNIQUEIDENTIFIER |
Storage size by type
Storage footprint drives both cost and speed: smaller rows mean more rows per page, fewer reads, and tighter indexes. The chart below shows representative fixed sizes in bytes for common types (variable types like VARCHAR and TEXT grow with content, so the bar reflects a typical short value).
Read these as guidance, not gospel: exact sizes vary by engine, by version, and by options such as fractional seconds precision on temporal types. The relationship holds everywhere though. Choosing INT over BIGINT where the range allows, or storing a UUID as 16 bytes instead of a 36 character string, compounds into real savings at scale.
Putting it together
Data types are the quiet foundation under every schema. Match each column to the smallest exact type that fits its real values: DECIMAL for money, VARCHAR for most text and CHAR only for fixed codes, a zone aware timestamp stored in UTC for real moments, and native UUID or JSON where they earn their place. Get these right and validation, storage, and performance all improve at once. When you are ready to practise on real tables, the structured SQL beginner course and the quick SQL cheat sheet are the natural next steps.