What is a generated (computed) column in a relational table, and what is the practical difference between declaring it STORED and declaring it VIRTUAL?
answer
- GENERATED ALWAYS AS (expr) - engine owns the value
- STORED = on disk, free reads, wider rows
- VIRTUAL = computed on read, no storage
- same row only, deterministic only
- index needs STORED in most engines
basics
~20 sA column the engine derives from other columns of the same row via a fixed expression; you never write to it. STORED materializes the value on disk at write time (uses space, free to read, indexable). VIRTUAL stores nothing and recomputes it on every read.
solid answer
~50 sA generated column is declared `GENERATED ALWAYS AS (<expression>)`. The engine owns the value: you cannot INSERT or UPDATE it, and the expression may reference only other columns of the same row. - **STORED** - computed once at insert/update time and physically persisted. Reads are free, it can be indexed and referenced like an ordinary column, but it widens every row, adds write CPU and WAL/redo volume, and changing the expression means rewriting the table. - **VIRTUAL** - nothing is persisted; the value is computed each time it is selected. Writes stay cheap and adding the column is usually a metadata-only change, but every read pays the expression cost, and whether it can be indexed depends on the engine. Rule of thumb: STORED when the value is read or filtered far more often than written, or when you need an index on it; VIRTUAL when the table is write-heavy, the expression is trivial, and you mainly want a convenient named projection.
code
sql · 12 linesCREATE TABLE orders (
id bigint PRIMARY KEY,
unit_price_c integer NOT NULL,
qty integer NOT NULL,
email text NOT NULL,
total_c integer GENERATED ALWAYS AS (unit_price_c * qty) STORED,
email_lower text GENERATED ALWAYS AS (lower(email)) VIRTUAL
);
-- rejected: the engine owns generated values
INSERT INTO orders (id, unit_price_c, qty, email, total_c)
VALUES (1, 500, 2, '[email protected]', 999);go deeper
Know the definition, that the database computes and owns the value, and the one-line STORED-versus-VIRTUAL difference (on disk versus on read).
Add the cost model - row width, write amplification, and read CPU - plus the same-row and determinism restrictions, and which choice lets you build an index.
Pitch it as a materialization decision driven by read/write ratio and access paths, and mention the migration cost of adding or changing a STORED expression on a large hot table.
Frame it as where a derived value should live at all - column, view, materialized view, or application - and what each choice implies for schema evolution, storage growth, and cross-engine portability.
## The idea A generated column (SQL Server calls it a computed column, Oracle a virtual column) is a table column whose value is not supplied by the client but derived by the database from an expression over other columns of the same row. It is declared with the `GENERATED ALWAYS AS (...)` clause. `GENERATED ALWAYS` is the point: the value is always the engine's to compute, so any attempt to insert or update it is an error. That is what makes it different from a plain column that the application happens to fill in with the same formula - here the invariant cannot be violated by a buggy code path, a bulk load, or a manual fix-up in a database console. ## STORED A STORED (SQL Server: PERSISTED) generated column is evaluated at write time and the result is written into the row on disk like any other value. Consequences: - Reads cost nothing extra - the value is already there, so scans, sorts, and comparisons see a plain column. - It can be indexed everywhere that supports generated columns at all, and in most engines it can carry constraints such as UNIQUE or CHECK. - Every row gets wider. That means more pages to scan, more buffer cache consumed, more WAL/redo written per change, and possibly off-row storage for large text values. - Every INSERT and every UPDATE that touches an input column pays the expression's CPU cost, and re-evaluates it even if the result is unchanged. - Because the bytes are on disk, adding a STORED column to an existing large table generally requires rewriting the whole table, and changing the expression later requires the same rewrite. ## VIRTUAL A VIRTUAL generated column stores nothing. The expression is evaluated whenever the column is read, so the column is essentially a per-table named projection that the engine substitutes at query time. Consequences: - Zero storage, zero extra write cost, and adding one is usually an instant catalog-only change. - Every read that projects or filters on the column pays the CPU cost, once per row touched. For an expensive expression over a wide scan, that can dominate. - Indexability varies. MySQL can build a secondary index on a VIRTUAL column (the index materializes the computed value even though the row does not). PostgreSQL gained virtual generated columns in version 18 and does not allow indexing them; before 18 only STORED existed there. SQL Server can index a computed column only if you mark it PERSISTED or the expression is deterministic and precise. ## Choosing between them Ask three questions. First, read/write ratio: a column read on every query but written rarely argues for STORED; a column on a high-ingest table read only by the occasional report argues for VIRTUAL. Second, do you need an index, a unique constraint, or a foreign key on it? Then STORED is the portable answer. Third, how expensive is the expression? Concatenating two short strings is nothing; parsing JSON, normalizing Unicode, or running a heavy formula over millions of rows per scan is not. A useful mental model: STORED trades write cost and disk for read cost; VIRTUAL trades read cost for write cost and disk. It is the same materialization tradeoff you make between a view and a materialized view, but at column granularity. ## What both share Both kinds are read-only to clients, both may reference only the current row (no subqueries, no other tables, no aggregates), and both require a deterministic expression. Neither is a place to put audit timestamps or anything drawn from the environment - `now()` and `random()` are rejected. Both are visible to the query planner as ordinary column references, which is exactly why they are attractive: application code, ORMs, and ad-hoc SQL can select and filter on them without knowing the formula. ## Defaults and syntax notes When the keyword is omitted, engines differ: MySQL defaults to VIRTUAL, PostgreSQL 18 defaults to VIRTUAL, and older PostgreSQL requires STORED explicitly. Always write the keyword out so the intent survives a port and a code review.
- Can a generated column reference a column from another table, or an aggregate over the same table?No. The expression is evaluated per row with only that row's values in scope, so no subqueries, no joins, and no aggregates are allowed. Anything cross-row would have to be recomputed when some other row changed, which the per-row evaluation model cannot detect. Cross-row derivation belongs in a view, a materialized view, or explicit application/trigger logic.
- If a VIRTUAL column costs nothing to store, why not make everything virtual?Because the cost moves to read time and is paid per row per query. A virtual column over a 50-million-row table scanned by a nightly report is fine; the same expression evaluated in a filter on a hot OLTP path multiplies CPU on your least scalable tier. You also lose portable indexability, so predicates on the column may force full scans.
STORED is meal-prepping on Sunday: freezer space consumed, dinner instant. VIRTUAL is cooking each night: nothing in the freezer, but you pay every time you eat.
saying these in an interview costs you the question
- Saying you can INSERT or UPDATE a generated column with an explicit value (GENERATED ALWAYS forbids it)
- Claiming VIRTUAL columns save resources with no downside - they move cost to every read
- Believing a generated column can aggregate or look at other rows or tables
- Assuming any generated column can be indexed in any engine
- Using a generated column for now() or a random value - the expression must be deterministic