In
relational database
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
s a virtual column is a table
column
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
whose value(s) is automatically computed using other columns values, or another deterministic expression. Virtual columns are defined of
SQL:2003 as Generated Column, and are only implemented by some
DBMS
In computing, a database is an organized collection of data or a type of data store based on the use of a database management system (DBMS), the software that interacts with end users, applications, and the database itself to capture and ana ...
s, like
MariaDB
MariaDB is a community-developed, commercially supported Fork (software development), fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License. Developm ...
,
SQL Server,
Oracle
An oracle is a person or thing considered to provide insight, wise counsel or prophetic predictions, most notably including precognition of the future, inspired by deities. If done through occultic means, it is a form of divination.
Descript ...
,
PostgreSQL
PostgreSQL ( ) also known as Postgres, is a free and open-source software, free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transaction processing, transactions ...
,
SQLite
SQLite ( "S-Q-L-ite", "sequel-ite") is a free and open-source relational database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it ...
and
Firebird (database server)
Firebird and fire bird may refer to:
Mythical birds
* Phoenix (mythology), sacred firebird found in the mythologies of many cultures
** Fenghuang, sometimes called Chinese phoenix
* Vermilion bird, one of the four symbols of the Chinese constel ...
(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.
MariaDB
MariaDB
MariaDB is a community-developed, commercially supported Fork (software development), fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License. Developm ...
is a
MySQL
MySQL () is an Open-source software, open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A rel ...
fork. Virtual columns were added in the 5.2 tree.
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 function A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its c ...
s or
stored procedure
A stored procedure (also termed prc, proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database d ...
s
* 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
A foreign key is a set of attributes in a table that refers to the primary key of another table, linking these two tables. In the context of relational databases, a foreign key is subject to an inclusion dependency constraint that the tuples ...
, 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
InnoDB is a storage engine for the database management system MySQL and MariaDB. Since the release of MySQL 5.5.5 in 2010, it replaced MyISAM as MySQL's default table type. It provides the standard ACID-compliant transaction features, along wit ...
*
MyISAM
*
Aria
In music, an aria (, ; : , ; ''arias'' in common usage; diminutive form: arietta, ; : ariette; in English simply air (music), air) is a self-contained piece for one voice, with or without instrument (music), instrumental or orchestral accompan ...
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:
ENERATED ALWAYS AS ( ) PERSISTENT NIQUE NIQUE KEY ">OMMENT
* ''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.
Oracle
Since version 11g,
Oracle
An oracle is a person or thing considered to provide insight, wise counsel or prophetic predictions, most notably including precognition of the future, inspired by deities. If done through occultic means, it is a form of divination.
Descript ...
supports virtual columns.
SQL Server
Microsoft SQL Server
Microsoft SQL Server is a proprietary relational database management system developed by Microsoft using Structured Query Language (SQL, often pronounced "sequel"). As a database server, it is a software product with the primary function of ...
supports virtual columns, but they are called Computed Columns.
SQL Server supports both persisted and non-persisted computed columns.
Firebird
Firebird
Firebird and fire bird may refer to:
Mythical birds
* Phoenix (mythology), sacred firebird found in the mythologies of many cultures
** Fenghuang, sometimes called Chinese phoenix
* Vermilion bird, one of the four symbols of the Chinese constella ...
has always supported virtual columns as its precursor
InterBase
InterBase is a relational database management system (RDBMS) currently developed and marketed by Embarcadero Technologies. It runs on the operating systems Microsoft Windows, macOS, Linux, Oracle Solaris, Solaris, Android (operating system), Andr ...
supports it, called Computed Columns.
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 or when adding columns to an existing table. The syntax used to define a virtual column is the following:
column_name ypeCOMPUTED BY (expression)
or the industry standard
column_name ypeGENERATED ALWAYS AS (expression)
PostgreSQL
Since version 12,
PostgreSQL
PostgreSQL ( ) also known as Postgres, is a free and open-source software, free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transaction processing, transactions ...
supports virtual columns, known as generated columns.
SQLite
Since version 3.31.0 (2020-01-22),
SQLite
SQLite ( "S-Q-L-ite", "sequel-ite") is a free and open-source relational database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it ...
supports virtual columns, known as generated columns.
See also
*
Derived column
References
{{reflist
External links
Virtual Columnsin MariaDB's documentation.
MariaDB 5.2: What would you use virtual columns for?on OpenLife.cc
Virtual Columns in Oracle Database 11g Release 1Computed Columns in SQL Server 2008
Database management systems