Part 1 — The relational foundation › Chapter 03

Data types and what they cost

Money is never a float. Also varchar vs nvarchar, dates, UUIDs as keys, and storage that becomes speed.

From the advert

“Ottima conoscenza del linguaggio SQL” — the fluency being asked for starts here. Type choice is the first place a schema is either right or quietly wrong for years.

Money is never a float

🧒 Simple

A computer stores 0.1 the way you would store one third as 0.3333 — close, but not exact. Add enough of those tiny errors together and an invoice for €10.00 becomes €9.999999998. Nobody notices until the accountant does, and then everybody notices.

🎓 Professional

FLOAT and REAL are binary floating point (IEEE 754). Decimal fractions like 0.1 have no exact binary representation, so arithmetic accumulates error and equality comparisons are unreliable. Two safe choices:

DECIMAL(p, s) / NUMERIC(p, s) — exact decimal arithmetic. DECIMAL(12, 2) is the default answer for currency in a European business system.
Integer minor units — store cents in a BIGINT (total_cents). Fast, exactly comparable, impossible to accidentally average into a fraction of a cent, and it makes the unit explicit in the column name. This is what the reference database does — run the playground and look at products.list_price_cents.

The one demonstration that settles the money argument: the same sum, in binary floating point and in exact decimal.

-- Run this in the playground. It is not a trick; it is how binary floats work.
SELECT 0.1 + 0.2 = 0.3        AS float_says;      -- 0 / false: the values are not equal
SELECT CAST(0.1 AS DECIMAL(4,2)) + CAST(0.2 AS DECIMAL(4,2))  -- exact, base-10 arithmetic
     = CAST(0.3 AS DECIMAL(4,2)) AS decimal_says;  -- 1 / true: exactly equal, every time

The conversion that causes the result

0.1 written bare is a binary floating-point literal in most engines. One tenth has no finite binary expansion, exactly as one third has no finite decimal one, so 0.1 is stored as the nearest representable value. Add two of those and the error survives: the sum is near 0.3 and not equal to it.
CAST(0.1 AS DECIMAL(4,2)) — converts to an EXACT type with four digits, two after the point. DECIMAL arithmetic is done in base 10, so a tenth is a tenth and the comparison is TRUE.
The rule this proves: never store money in FLOAT or REAL. Use DECIMAL(19,4), or integer cents as this course's reference database does.

⚠ Trap

MONEY in SQL Server looks like the right answer and is not. It is a scaled integer with four decimal places and it truncates intermediate results: dividing then multiplying loses precision in a way DECIMAL does not. Microsoft's own guidance is to use DECIMAL. Knowing this is a genuine differentiator in a SQL Server interview.

Strings: length, unicode and collation

Type	Use it when	Note
`CHAR(n)`	Every value is genuinely n characters	Blank-padded to n. Country codes, currency codes. Almost nothing else.
`VARCHAR(n)`	The default for text with a sane upper bound	The limit is a constraint, not an optimisation — storage is by actual length.
`NVARCHAR(n)`	SQL Server, any non-ASCII text	UTF-16, so two bytes per character. In PostgreSQL/MySQL just use `VARCHAR` with a UTF-8 database.
`TEXT` / `VARCHAR(MAX)`	Genuinely unbounded prose	Stored off-page. Cannot be a full index key in most engines.

🎓 Collation is a correctness setting, not a formatting one

Collation decides both sort order and equality. Under a case-insensitive collation 'Rossi' = 'rossi' is true, and a UNIQUE constraint will reject the second one. Under an accent-insensitive collation 'Perù' = 'Peru'. So a collation change is a behaviour change to every query in the database — and mixing collations across joined columns causes an implicit conversion that kills index usage. Choose one at creation and mean it; Latin1_General_CI_AI or an ICU locale is the usual Italian answer.

Dates, times, and the one that has a time zone

There are three genuinely different things, and engines name them badly:

A date — a birthday, an invoice date. No time, no zone. DATE.
A wall-clock time with no zone — “the shop opens at 09:00”, which stays 09:00 when the clocks change. TIMESTAMP WITHOUT TIME ZONE, DATETIME2.
An instant — when something actually happened. This is the one that needs a zone. TIMESTAMP WITH TIME ZONE (PostgreSQL timestamptz, SQL Server DATETIMEOFFSET).

⚠ Trap

Storing local time for an event is a bug with a delay fuse. On the last Sunday of October, 02:30 happens twice in Italy, and rows recorded in that hour cannot be ordered or subtracted correctly ever again. Store instants in UTC (or in a zone-aware type) and convert at the edge, when you display. Chapter 17 is the arithmetic.

Integers, identities and UUIDs as keys

🎓 The UUID question, answered properly

“Should I use a UUID as a primary key?” is asked constantly and answered badly. The real trade-off is insertion locality, and it depends on whether the key decides physical order.

A random UUID (v4) as a clustered key means every insert lands on a random page. That page must be read, modified, split when full — so the write working set becomes the whole table instead of the last page. On a large table this is a genuine, measurable collapse.
UUID v7 fixes it: the first 48 bits are a millisecond timestamp, so values are time-ordered and inserts stay at the end of the index. SQL Server's NEWSEQUENTIALID() solves the same problem the same way.
A BIGINT identity is narrower (8 bytes vs 16) and every non-clustered index carries a copy of the clustering key — so a wide key makes every index bigger.

✔ Say this in an interview

“UUIDs are useful when IDs must be generated outside the database — offline clients, or merging data from several systems. If I use one as a clustered key I want v7 or a sequential variant, because random keys scatter inserts across the whole index and cause page splits.”

Booleans, enums and “status” columns

A status is a small closed set of values, and there are three ways to represent it. Ranked:

A lookup table with a foreign key. Portable, self-documenting, joinable, and you can add a status without a migration to a constraint. This is the default.
A CHECK constraint on a short VARCHAR. Readable in raw query output, which matters more than people expect at 2am. Adding a value is an ALTER.
A native ENUM (PostgreSQL, MySQL). Compact, but reordering or removing a value is awkward and it is not portable.

⚠ Trap

Storing a status as a bare integer with the meaning living only in application code is the worst of all worlds: WHERE status = 3 is unreadable in a report, unverifiable in a bug ticket, and there is nothing stopping somebody writing 7. If you inherit this, add the lookup table and a foreign key — you can do it without changing the column.

Never FLOAT for money. DECIMAL(p, s) or integer minor units, and put the unit in the column name.
MONEY in SQL Server truncates intermediate results. Use DECIMAL.
A VARCHAR length is a business constraint, not a performance tuning knob.
Collation decides equality as well as sort order, so it is a correctness setting. Mixing collations across a join costs you the index.
Store instants in UTC or a zone-aware type; store wall-clock times and dates as themselves. Convert at the edge.
Random UUIDs as clustered keys scatter inserts and split pages. Use v7 or a sequential variant, or keep a narrow integer key.
Every non-clustered index carries the clustering key, so a wide primary key makes every index wider.
A status belongs in a lookup table or a CHECK constraint — not in an untyped integer whose meaning lives in application code.

Interview questions this chapter answers

Which type would you use for a price, and why?

DECIMAL(12,2), or an integer number of cents. Both are exact; floats are not, and currency arithmetic that is 0.0000001 out will eventually be compared for equality by a reconciliation job. If the system does high-volume arithmetic I lean to integer cents because it is also faster and impossible to misread.

What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed length and blank-padded to n; VARCHAR(n) stores only what you put in, plus a small length prefix. CHAR is right for values that genuinely are always the same length — a two-letter country code — and wrong for everything else, where it wastes space and produces surprising trailing-space comparisons.

How would you store a timestamp for an audit record?

As an instant, in UTC or a zone-aware type — timestamptz or DATETIMEOFFSET. Local time is ambiguous for one hour every autumn, which makes audit rows in that hour impossible to order correctly. Display conversion happens in the presentation layer.