virtual column

There are two types of virtual columns:

• Virtual columns
• Persistent columns

Virtual columns values are computed on the fly when needed, for example when they are returned by a SELECT statement. Persistent column values are computed when a row is inserted in a table, and they are written like all other values. They can change if other values change. Both virtual and persistent columns have advantages and disadvantages: virtual columns don't consume space on the disk, but they must be computed every time a query refers to them; persistent columns don't require any CPU time, but they consume disk space. However, sometimes a choice is not available, because some DBMS's support only one column type (or neither of them).

IBM Db2 supports Virtual column of Version 8 as Generated column.[https://public.dhe.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2s2e80.pdf SQL Reference Volume 2 Version 8]

MariaDB is a MySQL fork. Virtual columns were added in the 5.2 tree.[https://kb.askmonty.org/en/virtual-columns/ Virtual Columns]

Expressions that can be used to compute the virtual columns have the following limitations:

• They must be deterministic
• They cannot return constant values
• They cannot use user-defined functions or stored procedures
• They cannot include other virtual columns
• They cannot make use of subqueries

Persistent columns can be indexed and can be part of a foreign key, with a few small limitations concerning constraint enforcement.

Virtual columns can only be used on tables which use a storage engine which supports them. Storage engines supporting virtual columns are:

• InnoDB
• MyISAM
• Aria

MRG_MyISAM tables can be based on MyISAM tables which include persistent columns; but the corresponding MRG_MyISAM column should be defined as a regular column.

A CREATE TABLE or ALTER TABLE statement can be used to add a virtual column. The syntax used to define a virtual column is the following:

[GENERATED ALWAYS] AS ( ) [VIRTUAL | PERSISTENT] [UNIQUE] [UNIQUE KEY] [COMMENT ]

• type is the column's data type
• expression is the SQL expression which returns the column's value for each row
• text is an optional column comment

Support for virtual columns, known in MySQL as generated columns, started becoming available in MySQL 5.7. Various limitations on their use have been relaxed in subsequent versions.

{{Cite web|url=https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html|title=MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.7 CREATE TABLE and Generated Columns}}

Since version 11g, Oracle supports virtual columns.{{cite web| url=https://www.oracle.com/technetwork/articles/sql/11g-schemamanagement-089869.html | archive-url=https://web.archive.org/web/20170806022510/https://www.oracle.com/technetwork/articles/sql/11g-schemamanagement-089869.html | archive-date=2017-08-06 | title=Oracle 11g Schema Management}}

Microsoft SQL Server supports virtual columns, but they are called Computed Columns.{{cite web | url=http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx | title=Computed Columns | date=4 October 2012 }}

SQL Server supports both persisted and non-persisted computed columns.

Firebird has always supported virtual columns as its precursor InterBase supports it, called Computed Columns.{{Cite web|url=http://www.firebirdsql.org/refdocs/langrefupd25-ddl-table.html|title = TABLE}}

Firebird supports virtual columns, not persistent ones and allows for sub-selects, calling built in functions, external functions and stored routines in the virtual column expression.

Creating a virtual column can be done during table creation or when adding columns to an existing table. The syntax used to define a virtual column is the following:

column_name [type] COMPUTED BY (expression)

or the industry standard

column_name [type] GENERATED ALWAYS AS (expression)

Since version 12, PostgreSQL supports virtual columns, known as generated columns.{{Cite web|url=https://www.postgresql.org/docs/12/ddl-generated-columns.html|title = 5.3. Generated Columns|date = 12 August 2021}}

Since version 3.31.0 (2020-01-22), SQLite supports virtual columns, known as generated columns.{{Cite web|url=https://www.sqlite.org/gencol.html|title = Generated Columns}}

• Derived column

{{reflist}}

• [https://mariadb.com/kb/en/virtual-columns/ Virtual Columns] in MariaDB's documentation.
• [http://openlife.cc/blogs/2010/october/what-would-you-use-virtual-columns MariaDB 5.2: What would you use virtual columns for?] on OpenLife.cc
• [http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php Virtual Columns in Oracle Database 11g Release 1]
• [http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx Computed Columns in SQL Server 2008]

Category:Database management systems