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 not part of any SQL standard, and are only implemented by some DBMS's, like MariaDB, SQL Server, Oracle and Firebird (database server) (COMPUTED BY syntax).

Implementation

There are two types of virtual 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).

MariaDB

MariaDB is a MySQL fork. Virtual columns were added in the 5.2 tree. This feature is MariaDB specific, and is not supported by MySQL.[1]

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

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>]

Oracle

Oracle has supported virtual columns since the first release of version 11g .[2]

Oracle supports only virtual columns, not persistent ones. However, Oracle has another feature which allows the storage of the result of a query: materialized views.

Syntax

To create a virtual column, either a CREATE TABLE or an ALTER TABLE statement can be used. The syntax used to define a virtual column is the following:

column_name [type] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

SQL Server

Microsoft SQL Server supports virtual columns, but they are called Computed Columns.[3]

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

Firebird

Firebird has always supported virtual columns as it's precursor InterBase supports it, they're called Computed Columns.[4]

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)

Notes

  1. ↑ Virtual Columns
  2. ↑ http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php
  3. ↑ http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx
  4. ↑ http://www.firebirdsql.org/refdocs/langrefupd25-ddl-table.html
This article is issued from Wikipedia - version of the 12/1/2014. The text is available under the Creative Commons Attribution/Share Alike but additional terms may apply for the media files.