How does GENERATED ALWAYS AS IDENTITY differ from GENERATED ALWAYS AS (expression)?

## Two declarations, one keyword prefix SQL reuses `GENERATED ALWAYS AS` for two unrelated features, and the disambiguator is what follows: ```sql id BIGINT GENERATED ALWAYS AS IDENTITY -- sequence generator line_total NUMERIC(12,2) GENERATED ALWAYS AS (qty * price) STORED -- computed column ``` Read the token after `AS`. The bare keyword `IDENTITY` means "take the next number from a counter". A parenthesised expression means "compute this from the rest of the row". ## Where the value comes from An identity column's value is **row-independent**. The generator knows nothing about `qty` or `price`; it hands out 1, 2, 3 in insertion order and never looks at the data. Two rows with identical contents get different identity values — that is the entire point of a surrogate key. A generated column's value is **row-determined**. It is a function of other columns in the *same* row, so two rows with identical inputs necessarily get identical results. It cannot reference other rows, cannot contain a subquery, and cannot call a non-deterministic function such as one returning the current time or a random number — those restrictions exist so the value stays reproducible from the row alone. ## When the value is produced An identity value is assigned once, at INSERT, and then it just sits there — nothing in the row can change it, and no later UPDATE recomputes it. A generated column has no independent existence: it tracks its inputs. Change `qty` with an UPDATE and `line_total` changes with it, without the UPDATE mentioning `line_total` at all. That is what makes it useful — the derived value cannot drift out of sync with its sources, which is exactly what happens when the same value is maintained by application code. ## The write rule they share, and where it differs Both carry `ALWAYS`, and in both cases that means the statement may not supply a value: ```sql INSERT INTO order_lines (qty, unit_price) VALUES (3, 10.00); -- fine INSERT INTO order_lines (qty, unit_price, line_total) VALUES (3, 10.00, 999.00); -- error INSERT INTO order_lines (qty, unit_price, line_total) VALUES (3, 10.00, DEFAULT); -- fine UPDATE order_lines SET line_total = 0 WHERE line_id = 1; -- error ``` The difference is the escape hatch. An identity column has one: `OVERRIDING SYSTEM VALUE` on the INSERT, and the alternative declaration `GENERATED BY DEFAULT AS IDENTITY` which accepts supplied values outright. A generated column has neither. There is no `BY DEFAULT` form of a computed column and no per-statement override, because a stored value that contradicted the expression would make the column a lie. The only legal ways to name it in an INSERT are to omit it or to write `DEFAULT`, and in an UPDATE you may write `DEFAULT` (or, more usually, just leave it alone). ## The trailing storage keyword A generated column's declaration usually ends with a storage keyword — `STORED` means the computed value is materialised with the row; `VIRTUAL` means it is computed when read. MySQL accepts both and treats `VIRTUAL` as the default when neither is written; other engines accept a subset, so check yours before assuming. Which one to pick is a storage-and-index tradeoff rather than a question about the language; syntactically, the point is simply that this keyword slot exists on the expression form and has no counterpart on the identity form. Identity's parenthesised slot holds something entirely different — sequence options like `START WITH` and `INCREMENT BY`. ## Choosing between them They are not alternatives, and a well-formed table often has both, as the example above shows. The question to ask is what the value *means*: - "This row needs an identifier no client should choose" → identity column. - "This value is a pure function of other columns and must never disagree with them" → generated column. - "This value is derived but not deterministic, or depends on other rows" → neither; a generated column will be rejected, and you need a different mechanism. The misuse to avoid is trying to make an identity column meaningful — encoding a year, a region code or a checksum into it. The generator has no access to the row, so anything of that shape has to be a generated column, a `DEFAULT` expression, or ordinary application logic. ## Common mistakes Writing `GENERATED ALWAYS AS (IDENTITY)` with parentheses, which parses as an expression form and fails; expecting a supplied `line_total` to be accepted "as an override"; putting a `SELECT` or a current-timestamp call inside the expression; and assuming a generated column can be updated directly to correct bad data, when the fix has to be applied to its inputs.