A 10-million-row column holds one of six department names. What is actually stored when it is kept as codes, and what does that buy?

## What a dictionary-coded column is physically made of An ordinary column stores one value per row in a single **column representation** — the one physical form every value in that column is stored in. If the values are text, each row holds a text value, and if the same handful of words repeats, each repetition is stored again. A **dictionary-coded column** — repeated values held as codes — replaces that single buffer with two pieces: 1. **The value set.** Each distinct value is stored exactly once, in a small structure attached to the column. Six department names means six names stored, whatever the row count is. 2. **The code buffer.** One short whole number per row — the **stored code** — saying which member of the value set that row holds. The column has lost nothing. Asked for the value in row four million, it reads that row's code and looks it up. The change is purely one of representation: the same logical column, physically arranged as *say it once, point at it many times*. ## What the arrangement buys - **The per-row cost stops depending on the value.** A row pays a narrow fixed-width whole number instead of a value whose size is a property of the value itself. A long department name and a short one then cost the same per row. - **The distinct values are paid for once.** Ten million rows of six names store six names. - **Equality can become a whole-number comparison.** To find the rows equal to one particular name, a design that supports it resolves that name to its code once and then compares fixed-width whole numbers down the code buffer, instead of comparing values row by row. - **Splitting rows by this column has its slots ready-made.** The codes are already dense small whole numbers, which is exactly what a split wants as a slot number. - **The possible values become explicit.** The value set is something you can read off the column. On a plain column you would have to scan every row to learn the same thing. ## What is not uniform across designs This is a family of designs, not one design, and an answer that asserts any of the following flatly is wrong somewhere: | Question | Some designs | Others | |---|---|---| | Where the value set comes from | Declared when the table is built; a value outside it is refused or recorded as absent | Discovered from the data and extended whenever a new value appears | | How wide a stored code is | Chosen from the distinct count, as narrow as it can be | One fixed width, regardless of how few values there are | | Whether operations stay in coded form | Filtering, equality and splitting run on codes end to end | The column is decoded to full values at the first operation, so the saving lasts only while it sits still | | Where comparison order comes from | The values themselves | An order declared on the value set, or the codes as they were assigned | The practical consequence of the third row is the one candidates miss: the size you measured just after building the column is not necessarily the size the pipeline runs at. ## Where the idea earns its place, and where it does not The arrangement is a bet on repetition. It pays when the number of distinct values is a small fraction of the number of rows, because then each value is paid for once and each row pays only a code. It is worth nothing — or worse than nothing — when nearly every row is different, because then you store every value once *and* a code per row on top. A column of order identifiers is the standard counter-example; a column of country names, statuses or product tiers is the standard case for it. Two smaller cautions are worth saying out loud in an interview: - **A stored code is not a quantity.** It is an offset into one particular value set. Averaging codes, or comparing a code from one column against a code from another, is meaningless even when both columns hold the same labels. - **Reading one row costs an extra step.** Getting a row's actual value is a lookup rather than a read. For whole-column work that cost disappears into the pass; for code that touches rows one at a time it does not. ## How to answer it in an interview Name the two pieces, say what each costs — the value set once, a code per row — and then name the win beyond size: equality and splitting can run on narrow whole numbers. Then, unprompted, state the condition: the distinct count has to be small relative to the row count, and the saving only survives if the operations you actually run can stay in coded form. A candidate who volunteers the condition is telling you they have measured this rather than read about it.