Virtual column

Last updated

In relational databases a virtual column is a table column whose value(s) 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).

Contents

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).

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:

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>[GENERATEDALWAYS]AS(<expression>)[VIRTUAL|PERSISTENT][UNIQUE][UNIQUEKEY][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 or when adding columns to an existing table. The syntax used to define a virtual column is the following:

column_name[type]COMPUTEDBY(expression)

or the industry standard

column_name[type]GENERATEDALWAYSAS(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]

See also

Related Research Articles

Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.

<span class="mw-page-title-main">SQLite</span> Serverless relational database management system (RDBMS)

SQLite is a 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 belongs to the family of embedded databases. It is the most widely deployed database engine, as it is used by several of the top web browsers, operating systems, mobile phones, and other embedded systems.

The SQL SELECT statement returns a result set of records, from one or more tables.

An SQL INSERT statement adds one or more records to any single table in a relational database.

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

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 with foreign key support. It is included as standard in most binaries distributed by MySQL AB, the exception being some OEM versions.

In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.

A relational database management system uses SQL MERGE statements to INSERT new records or UPDATE existing records depending on whether condition matches. It was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.

A spatial database is a general-purpose database that has been enhanced to include spatial data that represents objects defined in a geometric space, along with tools for querying and analyzing such data.

An entity–attribute–value model (EAV) is a data model optimized for the space-efficient storage of sparse—or ad-hoc—property or data values, intended for situations where runtime usage patterns are arbitrary, subject to user variation, or otherwise unforeseeable using a fixed design. The use-case targets applications which offer a large or rich system of defined property types, which are in turn appropriate to a wide set of entities, but where typically only a small, specific selection of these are instantiated for a given entity. Therefore, this type of data model relates to the mathematical notion of a sparse matrix.

In relational databases, the information schema is an ANSI-standard set of read-only views that provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information that some databases make available through non-standard commands, such as:

 => SELECT count(table_name) FROM information_schema.tables;  count   -------  99    => SELECT column_name, data_type, column_default, is_nullable  FROM information_schema.columns WHERE table_name='alpha';  column_name | data_type | column_default | is_nullable   -------------+-----------+----------------+-------------  foo | integer | | YES  bar | character | | YES    => SELECT * FROM information_schema.information_schema_catalog_name;  catalog_name   --------------  johnd

The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. It is suitable for use in selecting a pseudo column such as SYSDATE or USER.

An embedded database system is a database management system (DBMS) which is tightly integrated with an application software; it is embedded in the application. It is a broad technology category that includes:

A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures.

Web2py is an open-source web application framework written in the Python programming language. Web2py allows web developers to program dynamic web content using Python. Web2py is designed to help reduce tedious web development tasks, such as developing web forms from scratch, although a web developer may build a form from scratch if required.

In database management systems (DBMS), a prepared statement, parameterized statement, or parameterized query is a feature where the database pre-compiles SQL code and stores the results, separating it from data. Benefits of prepared statements are:

PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database, Times Ten in-memory database, and IBM Db2. Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

SQL:2011 or ISO/IEC 9075:2011 is the seventh revision of the ISO (1987) and ANSI (1986) standard for the SQL database query language. It was formally adopted in December 2011. The standard consists of 9 parts which are described in detail in SQL. The next version is SQL:2016.

The following outline is provided as an overview of and topical guide to MySQL:

References

  1. ↑ SQL:2003 Has Been Published
  2. ↑ SQL Reference Volume 2 Version 8
  3. ↑ Virtual Columns
  4. ↑ "MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.7 CREATE TABLE and Generated Columns".
  5. ↑ "Oracle 11g Schema Management". Archived from the original on 2017-08-06.
  6. ↑ "Computed Columns". 4 October 2012.
  7. ↑ "TABLE".
  8. ↑ "5.3. Generated Columns". 12 August 2021.
  9. ↑ "Generated Columns".
This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.