Generated Columns in SQL: Virtual vs Stored, Indexes and Limits
Generated columns let the database compute a value from other columns, such as a line total, a lowercase email or a field pulled out of JSON, and keep it correct automatically. We tested virtual and stored generated columns in SQLite: writes, updates, indexes, file size, and every error we could trigger, then mapped the syntax to PostgreSQL, MySQL and SQL Server.
- Generated columns are defined with
GENERATED ALWAYS AS (expression)and are recomputed by the database whenever their inputs change. - Inserting or updating a generated column directly failed with “cannot INSERT into generated column”.
- For 50,000 rows, a stored generated column grew the database file from 2,904 KB (virtual) to 13,376 KB.
- An index on a generated
email_lowercolumn turned a 29 ms scan into an index search, but a query written aslower(email)did not use it.
Many tables carry values derived from other columns: an order total from quantity and price, a full name from first and last name, a normalised email for case-insensitive lookups. Computing them in application code works until one code path forgets, and then the stored value and its inputs disagree. Generated columns move the calculation into the table definition, so the database guarantees the value is always right.
We ran every test on SQLite 3.50.4 (generated columns have been supported since 3.31). Syntax for other databases is from their documentation and was not run.
What are generated columns?
A generated column, also called a computed or virtual column, has its value defined by an expression over other columns in the same row. You never insert or update it; the database works it out. There are two kinds: virtual columns are calculated when read and take no space, and stored columns are calculated when the row is written and saved on disk like normal columns.
Syntax
column_name type GENERATED ALWAYS AS (expression) VIRTUAL
column_name type GENERATED ALWAYS AS (expression) STORED
-- short form in SQLite and MySQL: the words GENERATED ALWAYS are optional
total REAL AS (qty * unit_price)
The expression can use other columns of the same row, constants and deterministic functions. If you omit VIRTUAL or STORED, SQLite and MySQL default to virtual.
A worked example
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
qty INT,
unit_price REAL,
total REAL GENERATED ALWAYS AS (qty * unit_price) VIRTUAL,
total_stored REAL GENERATED ALWAYS AS (round(qty * unit_price, 2)) STORED,
email TEXT,
email_lower TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL,
doc TEXT,
customer TEXT GENERATED ALWAYS AS (json_extract(doc, '$.customer.name')) VIRTUAL
);
INSERT INTO orders (id, qty, unit_price, email, doc)
VALUES (1, 3, 9.99, 'Asha@Example.com', '{"customer":{"name":"Asha"}}');
SELECT qty, unit_price, total, total_stored, email_lower, customer FROM orders;
-- [(3, 9.99, 29.97, 29.97, 'asha@example.com', 'Asha')]
We inserted only the real data. The total, the normalised email and the customer name extracted from a JSON document all appeared automatically. Generated columns also showed up in SELECT *, just like normal columns.
They stay correct automatically
UPDATE orders SET qty = 5 WHERE id = 1;
SELECT total, total_stored FROM orders;
-- (49.95, 49.95)
Changing the quantity updated both the virtual and the stored total. This is the main benefit: no code path, script or manual fix can leave a stale total behind, which is a common source of reporting errors when totals are maintained by application code.
You cannot write to them
INSERT INTO orders (id, qty, unit_price, total) VALUES (2, 1, 1, 999);
-- cannot INSERT into generated column "total"
UPDATE orders SET total = 1;
-- cannot UPDATE generated column "total"
The database rejects any attempt to set the value directly. Watch for this when migrating: bulk-load tools or ORMs that list every column in their INSERT statements must be told to skip generated columns, or loads that worked before will start failing. Copying data with INSERT INTO SELECT needs an explicit column list for the same reason.
Virtual vs stored
| Aspect | Virtual | Stored |
|---|---|---|
| Computed | On every read | On insert and update |
| Disk space | None | Like a normal column |
| Write cost | None | Extra work per write |
| Read cost | Expression evaluated each time | Plain column read |
| Add with ALTER TABLE in SQLite | Yes | No (“cannot add a STORED column”) |
Choose virtual for cheap expressions or rarely read values, and stored for expensive expressions read often, such as heavy JSON extraction or string processing on reporting tables. SQLite marks the two kinds differently in PRAGMA table_xinfo: our virtual columns had hidden flag 2 and the stored one flag 3.
Storage cost, measured
CREATE TABLE t (id INTEGER PRIMARY KEY, a TEXT,
b TEXT GENERATED ALWAYS AS (a || a || a || a) VIRTUAL); -- or STORED
-- 50,000 rows with a 50-character value in a
-- virtual: 2,904 KB stored: 13,376 KB
With a stored column that concatenated the source four times, the database file was 4.6 times larger, because every row now held the 200-character result as well. Virtual columns cost nothing on disk. On real tables the difference depends on the expression, but stored columns are never free, and they also make every write a little slower.
Indexing generated columns
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT,
email_lower TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL);
-- 200,000 rows
SELECT id FROM users WHERE email_lower = 'user123@example.com';
-- before index: SCAN users 29.02 ms
CREATE INDEX ix_el ON users(email_lower);
-- after index: SEARCH users USING INDEX ix_el (email_lower=?) under 0.01 ms
SELECT id FROM users WHERE lower(email) = 'user123@example.com';
-- SCAN users (the index on email_lower was not used)
You can index a generated column, even a virtual one, and the index stores the computed values, so lookups become fast. This is a clean way to support case-insensitive search. But SQLite’s planner matched the index only when the query referred to the column by name; the same expression written inline, lower(email), still scanned the table. Either query the generated column, or create an expression index on lower(email) instead. Check with the EXPLAIN plan, as our indexes guide recommends for every new index.
Pulling fields out of JSON
The customer column above extracted $.customer.name from a JSON document. This is one of the most useful patterns: keep flexible JSON in one column, expose the fields you filter and sort by as generated columns, and index them. Queries stay simple, and the JSON remains the single source of truth. See our SQL JSON functions guide for the extraction functions in each database.
Rules and limits
CREATE TABLE bad (a INT, b INT GENERATED ALWAYS AS (random()) VIRTUAL);
-- non-deterministic functions prohibited in generated columns
- Deterministic only: functions like
random()and the current time are refused, because the value must be the same every time it is computed. - Same row only: no subqueries, no other tables, no aggregates. For cross-table values use a view or a trigger.
- Not a primary key in SQLite, and other databases restrict how generated columns join foreign keys.
- Adding stored columns later requires rebuilding the table in SQLite; virtual ones can be added with ALTER TABLE.
- Expression changes usually mean dropping and re-adding the column.
Generated columns in other databases
| Database | Support (from documentation, not run) |
|---|---|
| PostgreSQL | Stored since version 12; version 18 added virtual generated columns, which are now the default kind |
| MySQL | Virtual and stored since 5.7; InnoDB supports secondary indexes on virtual columns |
| SQL Server | Computed columns: total AS (qty * price), add PERSISTED to store them |
| Oracle | Virtual columns: GENERATED ALWAYS AS (expr) VIRTUAL |
| SQLite | Virtual and stored since 3.31.0 (tested here) |
Generated columns vs views and triggers
- View: computes values at query time for a whole query, can join tables, but cannot be indexed in most databases. See our SQL views guide.
- Trigger: can maintain values from other tables, but is more code to write and test, and is easy to bypass or forget; see SQL triggers.
- Generated column: simplest and safest for values derived from the same row, and indexable.
Final thoughts
Generated columns put derived values where they belong: in the schema, always consistent with their inputs. Use virtual columns by default for cheap expressions and JSON fields, stored columns when an expression is expensive and read often, and index them to speed up lookups. In our tests they updated automatically, refused direct writes, cost nothing on disk when virtual, and turned a 29 ms scan into an instant lookup once indexed, as long as the query used the column name.
Frequently asked questions
What is a generated column in SQL?
A column whose value is calculated by the database from an expression over other columns in the same row. You cannot insert or update it directly, and it stays correct when its inputs change.
What is the difference between virtual and stored generated columns?
Virtual columns are computed when read and use no storage. Stored columns are computed when a row is written and saved on disk, which uses space but makes reads cheaper.
Can you index a generated column?
Yes. SQLite and MySQL allow indexes on virtual and stored generated columns, and PostgreSQL on stored ones. The index holds the computed values, so filtering on them is fast.
Why can I not use random or the current time in a generated column?
Generated columns must be deterministic, giving the same result for the same inputs every time, so functions such as random and the current timestamp are rejected.
Does PostgreSQL support virtual generated columns?
Yes, from PostgreSQL 18, where virtual is the default kind. Earlier versions from 12 onwards support only stored generated columns.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.