Virtual column
In relational databases a virtual column is a table column whose value is automatically computed using other columns values, or another deterministic expression. Virtual columns are defined of SQL:2003 as Generated Column,[1] and are only implemented by some DBMSs, like MariaDB, SQL Server, Oracle, PostgreSQL, SQLite and Firebird (database server) (COMPUTED BY syntax).
Implementation[]
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[]
IBM Db2 supports Virtual column of Version 8 as Generated column.[2]
MariaDB[]
MariaDB is a MySQL fork. Virtual columns were added in the 5.2 tree.[3]
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:
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.
Syntax[]
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:
<type> [GENERATED ALWAYS] AS ( <expression> ) [VIRTUAL | PERSISTENT] [UNIQUE] [UNIQUE KEY] [COMMENT <text>]
- 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.
MySQL[]
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. [4]
Oracle[]
Since version 11g, Oracle supports virtual columns.[5]
SQL Server[]
Microsoft SQL Server supports virtual columns, but they are called Computed Columns.[6]
SQL Server supports both persisted and non-persisted computed columns.
Firebird[]
Firebird has always supported virtual columns as its precursor InterBase supports it, called Computed Columns.[7]
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.
Syntax[]
Creating a virtual column can be done during table creation and 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)
PostgreSQL[]
Since version 12, PostgreSQL supports virtual columns, known as generated columns.[8]
SQLite[]
Since version 3.31.0 (2020-01-22), SQLite supports virtual columns, known as generated columns.[9]
Notes[]
- ^ SQL:2003 Has Been Published
- ^ SQL Reference Volume 2 Version 8
- ^ Virtual Columns
- ^ "MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.7 CREATE TABLE and Generated Columns".
- ^ http://www.oracle.com/technetwork/articles/sql/11g-schemamanagement-089869.html
- ^ http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx
- ^ "TABLE".
- ^ "5.3. Generated Columns". 12 August 2021.
- ^ "Generated Columns".
External links[]
- Virtual Columns in MariaDB's documentation.
- MariaDB 5.2: What would you use virtual columns for? on OpenLife.cc
- Virtual Columns in Oracle Database 11g Release 1
- Computed Columns in SQL Server 2008
- Database management systems