Answer.

Computed columns are columns whose values are automatically generated based on other columns in the table. This concept emerged to simplify access to the results of typical calculations, so that formulas do not need to be repeated in every query. They can be declared as virtual (values are computed on each selection) or persisted (physically stored when data is updated).

The problem is that it is not possible to compute for all expressions at the table level (limitations on the types of functions), and physically storing derived data takes up space and requires maintenance for freshness during UPDATE.

Solution: declare computed columns at the DDL level when possible, or implement update logic through triggers/procedures if storage is required or use aggregates.

Example code (MS SQL Server):

ALTER TABLE orders ADD total_sum AS (quantity * price); -- virtual
ALTER TABLE orders ADD total_sum_persisted AS (quantity * price) PERSISTED; -- persisted

Key features:

Virtual columns reduce redundancy, but decrease retrieval speed.
Persisted columns are updated automatically, but consume space and resources.
Some expressions are prohibited: non-replicable functions or subqueries cannot be used in computed columns.

Trick Questions.

Can an index be built on a computed column?

If the column is declared as PERSISTED (physically stored), an index can be built. For virtual columns— not always, it depends on the DBMS.

CREATE INDEX ix_total_sum ON orders (total_sum_persisted);

Are computed columns updated automatically when the source columns change?

Yes, when inserting/editing source data, the new value is computed automatically if the column is declared at the table level as computed.

What happens if a computed column references NULL?

Most DBMS return NULL if at least one operand is NULL— you must handle potential NULL values through COALESCE/ISNULL.

ALTER TABLE orders ADD total_with_discount AS (COALESCE(quantity * price, 0));

Common Mistakes and Anti-Patterns

Storing duplicate derived values unnecessarily, leading to increased volume.
Using complex, non-indexable expressions that slow down performance.
Inconsistent updates if calculations are done at the application level rather than the database.

Real-Life Example

Negative Case

The database stores the product sum and price as separate fields, and also separately— total. When updating the product, they forget to update total.

Pros:

Fast reading

Cons:

Data inconsistency, difficulties during recalculation.

Positive Case

The final sum is declared as a virtual column. It is always accurate, does not diverge from the source values.