SQL STORED

What does the SQL STORED keyword do?

STORED marks a generated column whose calculated value is persisted to disk instead of computed at query time.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL STORED:

SQL STORED Full Explanation

STORED is a column modifier that follows a GENERATED ALWAYS AS (expression) clause. When applied, the database evaluates the expression whenever an INSERT or UPDATE touches the row and then physically saves the resulting value in the table. Future SELECT statements read the pre-computed value, eliminating re-calculation at query time and allowing the column to be indexed. The opposite behavior is a VIRTUAL (or non-stored) generated column, which is calculated on the fly and does not occupy storage. Using STORED can improve read performance and enable indexing on derived data, but it consumes extra disk space and may slow writes because the value must be computed and written for every row change. In most engines, a STORED column cannot reference non-deterministic functions, and attempting to directly insert or update the column is disallowed because the value is automatically maintained by the database.

SQL STORED Syntax

-- Inside CREATE TABLE
CREATE TABLE table_name (
  col1 data_type,
  col2 data_type,
  generated_col data_type 
    GENERATED ALWAYS AS (expression) STORED
);

-- Adding later
ALTER TABLE table_name
  ADD COLUMN generated_col data_type 
    GENERATED ALWAYS AS (expression) STORED;

SQL STORED Parameters

Example Queries Using SQL STORED

-- Example 1: Persist full name for fast text search
CREATE TABLE employees (
  first_name VARCHAR(50),
  last_name  VARCHAR(50),
  full_name  VARCHAR(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)) STORED,
  INDEX full_name_idx (full_name)
);

-- Example 2: Materialize total price per line item
CREATE TABLE order_items (
  unit_price DECIMAL(10,2),
  quantity   INT,
  total_price DECIMAL(12,2) GENERATED ALWAYS AS (unit_price * quantity) STORED
);

-- Attempting to write to a STORED column (will fail)
INSERT INTO order_items (unit_price, quantity, total_price)
VALUES (9.99, 3, 29.97);

Expected Output Using SQL STORED

On CREATE TABLE or ALTER TABLE the database stores the definition
During INSERT or UPDATE it automatically calculates and saves the generated value
Manual writes to the STORED column throw an error

Use Cases with SQL STORED

Speed up reads on expensive expressions
Allow indexing of derived data such as concatenated strings or math formulas
Maintain invariant business rules (e.g., unit_price * quantity) without application logic
Persist frequently used JSON extractions for legacy clients that cannot compute them

Common Mistakes with SQL STORED

Forgetting that a STORED column uses disk space and slows writes
Attempting to INSERT or UPDATE the generated column directly
Using non-deterministic or disallowed functions in the expression (e.g., NOW())
Assuming all databases support STORED; some only allow virtual generated columns

First Introduced In

MySQL 5.7 (2015) and SQL:2011 standard

Frequently Asked Questions

What is the purpose of STORED in a generated column?

It persists the calculated value to disk, enabling rapid reads and indexing.

Can I update a STORED column directly?

No. The database manages the value; direct writes raise an error.

Does STORED consume extra storage?

Yes. Every row contains a physical copy of the generated value.

Which databases support STORED generated columns?

MySQL 5.7+, MariaDB 5.2+, PostgreSQL 12+, SQLite 3.31+, Oracle 12c+, SQL Server 2022+, among others.