What is a Generated Column?

Some database management systems (DBMSs) include a feature called generated columns.

Also known as “computed columns”, generated columns are similar to a normal column, except that a generated column’s value is derived from an expression that computes values from other columns.

In other words, a generated column’s value is computed from other columns.

Example

Here’s a basic example to demonstrate how generated columns work.

CREATE TABLE Products( 
    Id INTEGER PRIMARY KEY, 
    Name TEXT,
    Qty INT,
    Price INT,
    TotalValue INT GENERATED ALWAYS AS (Qty * Price)
);

This example was created in SQLite and uses the SQLite syntax for creating a generated column. This syntax uses the GENERATED ALWAYS column-constraint.

The GENERATED ALWAYS part is optional in SQLite, so you could just use AS.

Actually, the GENERATED ALWAYS part is also optional in MySQL, and it’s not even defined in TSQL (the SQL extension for SQL Server), so the AS keyword is all you need (along with the actual expression).

The expression is what determines the actual value that the column will hold. In my example, the part that reads (Qty * Price) is the expression that determines that value.

In this case, it’s simply multiplying the quantity (number of each product) with the price of each product.

Therefore this generated column contains the total value of each stock in the inventory.

If we insert data and select it, we can see the computed value of the generated column.

INSERT INTO Products VALUES 
  (1, 'Hammer', 10, 9.99),
  (2, 'Saw', 5, 11.34),
  (3, 'Wrench', 7, 37.00),
  (4, 'Chisel', 9, 23.00),
  (5, 'Bandage', 70, 120.00);

SELECT * FROM Products;

Result:

Id     Name        Qty    Price   TotalValue
-----  ----------  -----  ------  ----------
1      Hammer      10     9.99    99.9      
2      Saw         5      11.34   56.7      
3      Wrench      7      37      259       
4      Chisel      9      23      207       
5      Bandage     70     120     8400

If we look at the first row, we can see that there are 10 hammers at a price of 9.99 each. The generated column multiplies these two values together and we end up with 99.9.

If we reduce the number of hammers, then the generated column would produce a different value.

Here’s an example.

UPDATE Products 
SET Qty = 5 WHERE Id = 1;

SELECT * FROM Products;

Result:

Id     Name        Qty    Price   TotalValue
-----  ----------  -----  ------  ----------
1      Hammer      5      9.99    49.95     
2      Saw         5      11.34   56.7      
3      Wrench      7      37      259       
4      Chisel      9      23      207       
5      Bandage     70     120     8400

In this case I reduced the number of hammers to 5, and the generated column therefore calculated a value of 49.95.

Types of Generated Columns

There are two types of generated columns; stored and virtual.

Some DBMSs might use slightly different terminology, for example SQL Server uses the term “persistent” instead of “stored”. Either way, it refers to the same thing.

VIRTUAL: Column values are not stored, but are evaluated when rows are read.
STORED: Column values are evaluated and stored when rows are inserted or updated.

STORED columns therefore requires storage space, whereas VIRTUAL columns don’t.

However, this also means that VIRTUAL columns use more CPU cycles when being read.

Benefits of Generated Columns

Generated columns can save time when writing queries, reduce errors, and improve performance.

Generated columns can be used as a way to simplify and unify queries. A complex condition can be defined as a generated column and then referred to from multiple queries on the table. This helps ensure that they all use exactly the same condition.

Stored generated columns can be used as a materialized cache for complicated conditions that are costly to calculate on the fly.

Limitations of Generated Columns

Generated columns generally come with limitations when compared to normal columns, although the extent of the limitations may be determined by the DBMS.

For example, generated columns are generally only able to compute values from columns within the same row. However, this limitation can be overcome (in SQL Server at least) by using a user-defined function as part of the computed column’s expression.

See the links below to read more about the implementation of generated columns in various DBMSs.

Official Documentation

Here’s the documentation for some popular DBMSs that includes the limitations of their implementation of generated columns: