SQL Data Types & Memory Sizing | Learn Computer Academy

The type you pick per column isn't just about correctness — it's a direct memory and storage decision. Oversized columns waste space and slow down every index that touches them; undersized ones truncate real data in production. This lesson gives you concrete types and limits to reach for.

Numeric Types

Whole and decimal numbers, sized from 1 byte to 8+ bytes depending on the range you actually need.

TINYINT · SMALLINT · INT · BIGINT · DECIMAL(p,s) · FLOAT/DOUBLE

CREATE TABLE Product (
  id BIGINT UNSIGNED PRIMARY KEY,
  stock_count SMALLINT UNSIGNED,
  price DECIMAL(10,2)
);

TINYINT (1 byte, 0–255 unsigned) fits status codes and small counters.
SMALLINT (2 bytes, up to ~65k unsigned) fits stock counts and short IDs.
INT (4 bytes, ~4.2B unsigned) is the default safe choice for most primary keys.
BIGINT (8 bytes) is for IDs that will genuinely exceed 4 billion rows — most tables never need it.
DECIMAL(p,s) stores exact values with no floating-point rounding — always use it for money, never FLOAT/DOUBLE.

Common Mistakes

Defaulting every ID to BIGINT "just in case," doubling index size for no reason.
Storing money as FLOAT, which introduces silent rounding errors.

CHAR vs. VARCHAR

Both store text — CHAR is fixed-length and padded, VARCHAR is variable-length and stores only what you write plus a short length prefix.

CHAR(n) — always uses n bytes
VARCHAR(n) — uses actual length + 1–2 bytes overhead

CREATE TABLE Employee (
  country_code CHAR(2),
  full_name VARCHAR(100),
  email VARCHAR(254)
);

CHAR(2) always occupies exactly 2 bytes — ideal for fixed-width codes like country or currency codes.
VARCHAR(n) only stores what's actually written, so it's the right default for names, emails, and anything variable-length.
The (n) in VARCHAR is a maximum, not a reservation — a VARCHAR(255) column holding "Sam" uses roughly 4 bytes, not 255.

Common Mistake

Using CHAR for variable-length text, wasting space on padding for every short value.

Date & Time Types

Purpose-built types for calendar dates, timestamps, and durations — smaller and safer than storing dates as text.

DATE (3 bytes) · TIME (3 bytes) · DATETIME (8 bytes) · TIMESTAMP (4 bytes)

CREATE TABLE Employee (
  hire_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DATE stores just the calendar date — the smallest option when time-of-day doesn't matter.
TIMESTAMP is timezone-aware and more compact than DATETIME, but limited in range in some engines — check yours.
DATETIME has no timezone awareness but a much wider valid range.

Common Mistake

Storing dates as VARCHAR, which breaks sorting, range queries, and date math entirely.

Boolean, ENUM & JSON

Types for fixed small choices and semi-structured data, used sparingly to keep rows compact and queryable.

CREATE TABLE Employee (
  is_active BOOLEAN DEFAULT TRUE,
  status ENUM('active','on_leave','terminated'),
  metadata JSON
);

BOOLEAN is really a 1-byte TINYINT under the hood in most engines — cheap and clear.
ENUM stores a fixed list of string values as a compact integer internally, saving space over repeating text.
JSON is flexible but not indexable the way normal columns are — reach for it only when the shape of the data genuinely varies row to row.

Common Mistake

Using JSON as a substitute for proper columns because it's "flexible," making common queries slow and hard to index.

Production Sizing Cheatsheet

Concrete types and limits for the fields nearly every production schema has — a direct answer to "what should this column actually be."

Field	Recommended type	Why
full_name	VARCHAR(100)	30 truncates real names — many run 40–80+ characters with middle names/titles
first_name / last_name	VARCHAR(50) each	Splitting lets you sort, greet, and validate independently
email	VARCHAR(254)	RFC 5321 hard limit for a valid email address
password_hash	CHAR(60)	bcrypt output is always exactly 60 chars — CHAR avoids padding waste
phone	VARCHAR(20)	Covers "+", country code, extensions — never store as INT
uuid	CHAR(36) or BINARY(16)	36 for readable text form, 16 binary for a 2x+ smaller index
postal_code	VARCHAR(10)	International codes vary; some contain letters
country_code	CHAR(2)	Fixed-width ISO 3166-1 alpha-2
currency_amount	DECIMAL(19,4)	Exact precision, headroom for large sums and 4 decimal places
url	VARCHAR(2048)	Practical browser-enforced upper bound for a URL
ip_address	VARBINARY(16) or VARCHAR(45)	16 bytes binary covers IPv4 + IPv6 compactly
is_active	BOOLEAN	1 byte instead of a TINYINT or VARCHAR flag

A common real-world bug: sizing full_name VARCHAR(30) — names like "Priyanka Chattopadhyay" or "Jean-Baptiste van der Berg" already exceed 30 characters. Since VARCHAR only costs what's used, sizing to VARCHAR(100) instead of VARCHAR(30) has effectively zero memory cost for short names — the cap only matters for the rare long one.

Rule of Thumb

Pick the smallest fixed type that can never legitimately overflow (IDs, codes, flags), and be generous with VARCHAR caps since they don't pre-allocate space.

Common Mistakes

Sizing VARCHAR columns to the average case instead of the real maximum case.
Storing phone numbers or postal codes as INT, breaking leading zeros and "+" prefixes.

Memory Management Principles

A few rules that keep a schema lean at scale, beyond just picking types per column.

Fixed-width columns (CHAR, INT, DATE) pack more predictably into an engine's storage pages, which helps index performance.
Every extra byte on an indexed column is multiplied across every row in that index — sizing matters most on indexed and frequently-joined columns.
Repeated strings (department names, categories) are cheaper normalized into a lookup table referenced by a small INT foreign key than stored as text in every row.
NULL-able columns typically cost a bit less than storing an empty string, and mean something different — use NULL for "unknown," not empty text.

Diagram showing the four SQL data type families — numeric, text, date and time, and boolean/ENUM/JSON — each with example type names

With the right types and sizes in place, the next four lessons cover each SQL family in turn, starting with DDL — the commands that actually create the tables you've just learned to size.

Numeric Types

Whole and decimal numbers, sized from 1 byte to 8+ bytes depending on the range you actually need.

TINYINT · SMALLINT · INT · BIGINT · DECIMAL(p,s) · FLOAT/DOUBLE

CREATE TABLE Product (
  id BIGINT UNSIGNED PRIMARY KEY,
  stock_count SMALLINT UNSIGNED,
  price DECIMAL(10,2)
);

TINYINT (1 byte, 0–255 unsigned) fits status codes and small counters.
SMALLINT (2 bytes, up to ~65k unsigned) fits stock counts and short IDs.
INT (4 bytes, ~4.2B unsigned) is the default safe choice for most primary keys.
BIGINT (8 bytes) is for IDs that will genuinely exceed 4 billion rows — most tables never need it.
DECIMAL(p,s) stores exact values with no floating-point rounding — always use it for money, never FLOAT/DOUBLE.

Common Mistakes

Defaulting every ID to BIGINT "just in case," doubling index size for no reason.
Storing money as FLOAT, which introduces silent rounding errors.

CHAR vs. VARCHAR

Both store text — CHAR is fixed-length and padded, VARCHAR is variable-length and stores only what you write plus a short length prefix.

CHAR(n) — always uses n bytes
VARCHAR(n) — uses actual length + 1–2 bytes overhead

CREATE TABLE Employee (
  country_code CHAR(2),
  full_name VARCHAR(100),
  email VARCHAR(254)
);

CHAR(2) always occupies exactly 2 bytes — ideal for fixed-width codes like country or currency codes.
VARCHAR(n) only stores what's actually written, so it's the right default for names, emails, and anything variable-length.
The (n) in VARCHAR is a maximum, not a reservation — a VARCHAR(255) column holding "Sam" uses roughly 4 bytes, not 255.

Common Mistake

Using CHAR for variable-length text, wasting space on padding for every short value.

Date & Time Types

Purpose-built types for calendar dates, timestamps, and durations — smaller and safer than storing dates as text.

DATE (3 bytes) · TIME (3 bytes) · DATETIME (8 bytes) · TIMESTAMP (4 bytes)

CREATE TABLE Employee (
  hire_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DATE stores just the calendar date — the smallest option when time-of-day doesn't matter.
TIMESTAMP is timezone-aware and more compact than DATETIME, but limited in range in some engines — check yours.
DATETIME has no timezone awareness but a much wider valid range.

Common Mistake

Storing dates as VARCHAR, which breaks sorting, range queries, and date math entirely.

Boolean, ENUM & JSON

Types for fixed small choices and semi-structured data, used sparingly to keep rows compact and queryable.

CREATE TABLE Employee (
  is_active BOOLEAN DEFAULT TRUE,
  status ENUM('active','on_leave','terminated'),
  metadata JSON
);

BOOLEAN is really a 1-byte TINYINT under the hood in most engines — cheap and clear.
ENUM stores a fixed list of string values as a compact integer internally, saving space over repeating text.
JSON is flexible but not indexable the way normal columns are — reach for it only when the shape of the data genuinely varies row to row.

Common Mistake

Using JSON as a substitute for proper columns because it's "flexible," making common queries slow and hard to index.

Production Sizing Cheatsheet

Concrete types and limits for the fields nearly every production schema has — a direct answer to "what should this column actually be."

Field	Recommended type	Why
full_name	VARCHAR(100)	30 truncates real names — many run 40–80+ characters with middle names/titles
first_name / last_name	VARCHAR(50) each	Splitting lets you sort, greet, and validate independently
email	VARCHAR(254)	RFC 5321 hard limit for a valid email address
password_hash	CHAR(60)	bcrypt output is always exactly 60 chars — CHAR avoids padding waste
phone	VARCHAR(20)	Covers "+", country code, extensions — never store as INT
uuid	CHAR(36) or BINARY(16)	36 for readable text form, 16 binary for a 2x+ smaller index
postal_code	VARCHAR(10)	International codes vary; some contain letters
country_code	CHAR(2)	Fixed-width ISO 3166-1 alpha-2
currency_amount	DECIMAL(19,4)	Exact precision, headroom for large sums and 4 decimal places
url	VARCHAR(2048)	Practical browser-enforced upper bound for a URL
ip_address	VARBINARY(16) or VARCHAR(45)	16 bytes binary covers IPv4 + IPv6 compactly
is_active	BOOLEAN	1 byte instead of a TINYINT or VARCHAR flag

Rule of Thumb

Pick the smallest fixed type that can never legitimately overflow (IDs, codes, flags), and be generous with VARCHAR caps since they don't pre-allocate space.

Common Mistakes

Sizing VARCHAR columns to the average case instead of the real maximum case.
Storing phone numbers or postal codes as INT, breaking leading zeros and "+" prefixes.

Memory Management Principles

A few rules that keep a schema lean at scale, beyond just picking types per column.

Fixed-width columns (CHAR, INT, DATE) pack more predictably into an engine's storage pages, which helps index performance.
Every extra byte on an indexed column is multiplied across every row in that index — sizing matters most on indexed and frequently-joined columns.
Repeated strings (department names, categories) are cheaper normalized into a lookup table referenced by a small INT foreign key than stored as text in every row.
NULL-able columns typically cost a bit less than storing an empty string, and mean something different — use NULL for "unknown," not empty text.

With the right types and sizes in place, the next four lessons cover each SQL family in turn, starting with DDL — the commands that actually create the tables you've just learned to size.