View (SQL)

In database theory, a view is a virtual or logical 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.

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.
• Limit the exposure to which a table or tables are exposed to 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 (SQL:2003) 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, UPDATE, 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.

Equivalency

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

accounts view:
-------------
SELECT name,
       money_received,
       money_sent,
       (money_received - money_sent) AS balance,
       address,
	   ...
  FROM table_customers c 
  JOIN accounts_table a 
    ON a.customerid = c.customer_id

The application would simply run a simple query such as:

Sample query
------------
SELECT name,
       balance
  FROM accounts_view

The RDBMS then takes the simple query, replaces the equivalent view, then sends the following to the optimiser:

Preprocessed query:
------------------
SELECT name,
       balance
  FROM (SELECT name,
               money_received,
               money_sent,
               (money_received - money_sent) AS balance,
               address,
			    ...
          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.

External links

• Views in Microsoft SQL Server 2000
• Views in MySQL
• Views in PostgreSQL