Every column in a table has a data type that determines what kind of value it can hold and how it's stored, compared, and sorted.
Numeric Types
| Type | Use For |
|---|---|
| INT / INTEGER | Whole numbers — IDs, counts, quantities |
| DECIMAL(p,s) / NUMERIC(p,s) | Exact values — money, precise measurements |
| FLOAT / DOUBLE | Approximate decimal values — scientific data (avoid for money) |
String Types
| Type | Use For |
|---|---|
| CHAR(n) | Fixed-length text — e.g. a 2-letter state code |
| VARCHAR(n) | Variable-length text with a max length — names, emails |
| TEXT | Long, unbounded text — articles, descriptions |
Date & Time Types
| Type | Use For |
|---|---|
| DATE | Just a date — 2026-08-15 |
| TIME | Just a time — 14:30:00 |
| DATETIME / TIMESTAMP | Date and time together |
Boolean
MySQL doesn't have a true native BOOLEAN — it's stored as TINYINT(1) (0/1). PostgreSQL and SQL Server support a real BOOLEAN/BIT type.
Example
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10,2),
is_active BOOLEAN,
joined_on DATE
);Dialect Differences
| Concept | MySQL | PostgreSQL | SQL Server |
|---|---|---|---|
| Auto-incrementing ID | AUTO_INCREMENT | SERIAL / IDENTITY | IDENTITY(1,1) |
| Boolean | TINYINT(1) | BOOLEAN | BIT |
| Long text | TEXT | TEXT | VARCHAR(MAX) |
Common Mistakes
- Using FLOAT for money. Floating-point numbers can't represent values like 0.1 exactly, which causes rounding errors in financial totals — always use
DECIMALfor currency. - Using VARCHAR(255) for everything out of habit, without thinking about the actual max length needed.
- Storing dates as strings (e.g.
VARCHAR) instead of a realDATEtype — you lose the ability to sort and filter correctly.
Interview Relevance
Q: "Why shouldn't you store prices as FLOAT?" is a common one — the answer is binary floating-point representation causing rounding errors; DECIMAL stores exact values.
Practice Question
Choose an appropriate data type for: a phone number, a product price, an "in stock" flag, and a user's date of birth.
