In database theory
, a view
is a stored query accessible as a virtual table
composed of the result set of a query
. Unlike ordinary tables (base tables) in a relational database
, a view is not part of the physical schema
: it is a dynamic, virtual table computed or collated from data
in the database
. Changing the data
in a table
alters the data
shown in the view.
The result of a view is stored in a permanent table whereas the result of a query is displayed in a temporary table.
Views can provide advantages over tables:
- They can subset the data contained in a table
- They can join and simplify multiple tables into a single virtual table
- Views can act as aggregated tables, where aggregated data (sum, average etc.) are calculated and presented as part of the data
- Views can hide the complexity of data, for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
- Views take very little space to store; only the definition is stored, not a copy of all the data they present
- Depending on the SQL engine used, views can provide extra security.
- Views can limit the exposure to which a table or tables are exposed to the outer world
Just like functions (in programming) provide abstraction, views can be used to create abstraction. Also, just like functions, views can be nested, thus one view can aggregate data from other views. Without the use of views it would be much harder to normalise databases above second normal form. Views can make it easier to create lossless join decomposition.
Rows available through a view are not sorted. A view is a relational table, and the relational model states that a table is a set of rows. Since sets are not sorted - per definition - the rows in a view are not ordered either. Therefore, an ORDER BY clause in the view definition is meaningless and the SQL standard does not allow this for the subselect in a CREATE VIEW statement.
Read-only vs. updatable views
Views can be read-only
or updatable. If the database system is able to determine the reverse mapping from the view schema to the schema of the underlying base tables, then the view is updatable. INSERT
, and DELETE
operations can be performed on updatable views. Read-only views do not support such operations because the DBMS is not able to map the changes to the underlying base tables.
Some systems support the definition of INSTEAD OF triggers on views. This technique allows the definition of logic that shall be executed instead of an insert, update, or delete operation on the views. Thus, data modifications on read-only views can be implemented. However, an INSTEAD OF trigger does not change the read-only or updatable property of the view itself.
Advanced view features
Various database management systems
have extended the views from read-only subsets of data
The Oracle database
introduced the concept of materialized views
, which are pre-executed, non-virtual views commonly used in data warehousing
. They are a static snapshot of the data and may include data from remote sources. The accuracy of a materialized view depends on the frequency or trigger mechanisms behind its updates. DB2 provides so-called materialized query tables (MQTs)
for the same purpose. Microsoft SQL Server
, introduced in the 2000 version, indexed views which only store a separate index from the table, but not the entire data.
A view is equivalent to its source query. When queries are run against views, the query is modified. For example, if there exists a view named Accounts_view and the content is:
(money_received - money_sent) AS balance,
FROM table_customers c
JOIN accounts_table a
ON a.customerid = c.customer_id
The application would simply run a simple query such as:
The RDBMS then takes the simple query, replaces the equivalent view, then sends the following to the optimiser:
FROM (SELECT name,
(money_received - money_sent) AS balance,
FROM table_customers c JOIN accounts_table a
ON a.customerid = c.customer_id )
From this point on the optimizer takes the query, removes unnecessary complexity (i.e. it is not necessary to read the address, since the parent invocation does not make use of it) and then sends the query to the SQL engine for processing.