SQL VIRTUAL

What is the SQL VIRTUAL keyword?

Marks a generated column whose value is computed on read and not physically stored.

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 VIRTUAL:

SQL VIRTUAL Full Explanation

In MySQL and compatible systems, VIRTUAL designates a generated column whose value is calculated from an expression every time the row is read. Unlike STORED generated columns, virtual columns do not occupy disk space because their value is not persisted. They can be indexed (with some limitations), used in queries, and referenced by other generated columns. A virtual column is always read only: you cannot insert or update it directly. Performance is generally slower than STORED columns because the expression is evaluated at query time. Virtual columns are ideal when the derived value changes rarely, storage conservation is important, or the expression is simple enough to compute quickly. They were first added in MySQL 5.7 and are also available in MariaDB and Oracle, though syntax details differ. PostgreSQL and SQLite achieve similar behavior with other constructs but do not use the VIRTUAL keyword.

SQL VIRTUAL Syntax

CREATE TABLE table_name (
  column_name data_type,
  generated_col data_type AS (expression) VIRTUAL
);

ALTER TABLE table_name
  ADD COLUMN generated_col data_type AS (expression) VIRTUAL;

SQL VIRTUAL Parameters

Example Queries Using SQL VIRTUAL

-- Create with a virtual column
CREATE TABLE orders (
  id INT PRIMARY KEY,
  price DECIMAL(10,2),
  quantity INT,
  total DECIMAL(10,2) AS (price * quantity) VIRTUAL
);

-- Add a virtual column later
ALTER TABLE orders
  ADD COLUMN vat DECIMAL(10,2) AS (total * 0.2) VIRTUAL;

-- Query using the virtual columns
SELECT id, total, vat FROM orders WHERE total > 100;

Expected Output Using SQL VIRTUAL

The table is created with total and vat defined as virtual generated columns
When you run the SELECT, MySQL calculates total and vat on the fly for each row and returns the computed values without storing them

Use Cases with SQL VIRTUAL

Save disk space by avoiding duplicate derived data
Keep derived values always in sync with base columns without triggers
Quickly expose simple calculations such as totals, concatenations, or JSON extractions
Add business logic to legacy tables without rewriting application code

Common Mistakes with SQL VIRTUAL

Trying to INSERT or UPDATE a virtual column directly
Expecting virtual columns to improve read performance; they can slow reads because of on-the-fly evaluation
Using non-deterministic or disallowed functions (e.g., RAND()) in the expression
Assuming all storage engines allow virtual column indexing (MyISAM does not)
Forgetting that virtual columns cannot be used as partition keys

First Introduced In

MySQL 5.7

Frequently Asked Questions

What is the difference between virtual and stored generated columns?

Virtual columns compute their value when you read the row, while stored columns persist the computed value on disk. Stored columns read faster but use more space.

Can I update a virtual column directly?

No. A virtual column is read only. You must update the base columns referenced in its expression, and the virtual column value will adjust automatically.

Are virtual columns automatically indexed?

No. You need to create an index explicitly, and only deterministic, non-JSON functions are allowed in expressions for indexing in most engines.

Do virtual columns affect insert performance?

Inserts are slightly faster than with stored columns because nothing extra is written to disk, but the benefit is usually minor compared to overall workload.