1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
Generated columns in SQL: virtual vs stored, indexes and limits

Key takeaways

  • 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_lower column turned a 29 ms scan into an index search, but a query written as lower(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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Confusion Matrix Explained: Precision, Recall, F1 and Thresholds in Python

Confusion matrix explained with Python: TP, FP, FN, TN, precision, recall, F1, the accuracy trap, threshold choice by cost and…

Read article →
Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination explained: offset vs keyset and cursor pagination timed on 1 million rows, duplicates when data changes, the ties…

Read article →
DevOps

Git Submodule Explained: Add, Clone, Update and Remove Safely

Git submodule explained: add, clone, update –init, update –remote, the detached HEAD trap, foreach and clean removal, tested step by…

Read article →
Test Automation

Pytest tmp path Explained: tmp_path, tmp_path_factory and Temp Files

Pytest tmp_path explained: per-test temporary folders, tmp_path_factory for shared files, monkeypatch.chdir, legacy tmpdir, retention and basetemp.

Read article →
Software Testing

Test Case Prioritization: 6 Techniques Compared With APFD

Test case prioritization compared: risk, coverage, cost, history and random orders scored with APFD and time to find all faults…

Read article →
Databases & SQL

Full Text Search in SQL: FTS5, Ranking, Phrases and Speed

Full text search in SQL with SQLite FTS5: MATCH queries, phrases, prefixes, NEAR, stemming, BM25 ranking, snippets, two gotchas and…

Read article →
Databases & SQL

Generated Columns in SQL: Virtual vs Stored, Indexes and Limits

Generated columns in SQL explained: virtual vs stored, automatic updates, write errors, storage cost, indexing, JSON fields and support in…

Read article →
Software Development

Object Freeze in JavaScript: Shallow vs Deep, Seal and const

Object freeze in JavaScript explained: freeze vs seal vs const, strict mode errors, shallow vs deep freeze, frozen arrays, Map…

Read article →
Software Development

Python hashlib: SHA-256, File Hashes, HMAC and Password Hashing

Python hashlib explained: SHA-256 for strings and files, algorithm speeds, why passwords need PBKDF2 or scrypt, safe verification and HMAC…

Read article →
Software Development

Python UUID Explained: uuid1, uuid4, uuid5, uuid7 and Database Keys

Python UUID explained: uuid4, uuid5, uuid1 privacy, time-ordered uuid7, parsing, storage formats and UUIDs as database keys, tested on Python…

Read article →
Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →