SQL

Generated Columns in SQL

⚙️ Generated Columns are table columns whose values are automatically computed from other columns using an expression. Instead of storing manually entered values, the database calculates them whenever a row is inserted or updated, helping reduce data duplication and maintain consistency.

📖 What are Generated Columns?

A generated column derives its value from one or more existing columns in the same table. For example, if a table stores the quantity and unit price of a product, a generated column can automatically calculate the total price.

Information

Generated columns are also known as computed columns, virtual columns, or calculated columns, depending on the database system.

🎯 Why Use Generated Columns?

Generated columns eliminate repetitive calculations and ensure derived values remain accurate.

📌 Automatically calculate values.
📌 Reduce duplicate data.
📌 Improve data consistency.
📌 Simplify SQL queries.
📌 Minimize application-side calculations.

📚 Types of Generated Columns

Type	Description
Virtual Generated Column	Calculated whenever the value is read. Usually consumes little or no additional storage.
Stored Generated Column	Calculated when data is inserted or updated and stored in the table.

Important

Support for virtual and stored generated columns varies among database systems. Some databases implement similar functionality using computed columns.

📝 Basic Syntax (MySQL)

Generated Column Syntax

CREATE TABLE table_name
(
    column1 data_type,
    column2 data_type,

    generated_column data_type
    GENERATED ALWAYS AS (expression)
    VIRTUAL
);

💡 Create a Table with a Generated Column

Create a Products table where the total price is calculated automatically from the quantity and unit price.

Virtual Generated Column

CREATE TABLE Products
(
    ProductID INT PRIMARY KEY,
    Quantity INT,
    UnitPrice DECIMAL(10,2),

    TotalPrice DECIMAL(10,2)
    GENERATED ALWAYS AS
    (Quantity * UnitPrice) VIRTUAL
);

➕ Insert Data

Notice that the generated column is not included in the INSERT statement.

Insert Product

INSERT INTO Products
(ProductID, Quantity, UnitPrice)
VALUES
(101, 5, 120.00);

📊 Result

ProductID	Quantity	UnitPrice	TotalPrice
101	5	120.00	600.00

💾 Stored Generated Columns

A stored generated column calculates its value during data modification and saves the computed value in the table.

Stored Generated Column

CREATE TABLE Products
(
    ProductID INT PRIMARY KEY,
    Quantity INT,
    UnitPrice DECIMAL(10,2),

    TotalPrice DECIMAL(10,2)
    GENERATED ALWAYS AS
    (Quantity * UnitPrice) STORED
);

Stored generated columns may improve query performance for frequently accessed calculated values because the result is already stored.

🔄 Automatic Updates

Whenever one of the source columns changes, the generated column is updated automatically by the database.

Update Source Data

UPDATE Products
SET Quantity = 8
WHERE ProductID = 101;

The database automatically recalculates TotalPrice without requiring an additional update statement.

📊 Virtual vs Stored Generated Columns

Feature	Virtual	Stored
Stored on Disk	❌ Usually No	✅ Yes
Calculated During Read	✅ Yes	❌ No
Consumes Storage	Minimal or None	More
Read Performance	May require recalculation	Usually Faster
Write Performance	Usually Faster	May be Slightly Slower

💼 Real-World Example

An invoicing system stores the quantity and unit price of each product. A generated column automatically calculates the line total, ensuring invoices always display accurate amounts without requiring application-side calculations.

Invoice Items

CREATE TABLE InvoiceItems
(
    ItemID INT PRIMARY KEY,
    Quantity INT,
    UnitPrice DECIMAL(10,2),

    LineTotal DECIMAL(10,2)
    GENERATED ALWAYS AS
    (Quantity * UnitPrice) STORED
);

🗄️ Database Compatibility

Database System	Generated Column Support
MySQL	Supports VIRTUAL and STORED generated columns.
PostgreSQL	Supports stored generated columns using GENERATED ALWAYS AS (... ) STORED.
SQL Server	Supports computed columns, with optional persistence.
Oracle	Supports virtual columns and related features.
SQLite	Supports generated columns (virtual and stored) in modern versions.

⚠️ Common Mistakes

❌ Trying to manually insert values into generated columns.
❌ Using unsupported expressions for generated columns.
❌ Choosing a stored generated column when a virtual one would be sufficient.
❌ Assuming every database system supports identical syntax and features.

Warning

Generated columns should be based on deterministic expressions whenever required by the database system. Certain functions or expressions may not be permitted.

⚠️ Best Practices

Best Practice

Use generated columns for values derived from other columns, avoid storing duplicate information manually, choose virtual or stored columns based on workload and performance needs, keep expressions simple and deterministic, and verify feature support for your database system before implementation.

🚀 Key Points to Remember

📌 Generated columns are calculated automatically by the database.
📌 They reduce duplicate data and improve consistency.
📌 Virtual columns are typically calculated when queried.
📌 Stored generated columns save the calculated value in the table.
📌 The exact syntax and capabilities vary across database systems.
📌 Generated columns simplify queries and reduce application-side calculations.

>>"Generated columns let the database compute derived values automatically, keeping your data accurate and your queries simpler."

Summary

✅ Generated columns automatically calculate values from other columns, reducing redundancy and improving data integrity. Whether implemented as virtual, stored, or computed columns, they simplify database design by moving repetitive calculations into the database itself while ensuring derived values remain accurate and consistent.