View (SQL): Difference between revisions

Revision as of 09:20, 3 October 2006

Revision as of 17:20, 22 October 2006

Line 1:

Originally, inIn [[database theory]], a '''view''' is a [[read only]] virtual or logical [[Table (database)|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 [[database design|physical schema]]: it is a dynamic, virtual table computed or collated from [[data]] in the [[database]]. Changing the [[data]] in a [[Table (database)|table]] alters the [[data]] shown in the view.

Views can provide advantages over tables;

Line 10:

* Depending on the [[SQL]] engine used, views can provide extra security.

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 (SQL)|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 (SQL)|INSERT]], [[Update (SQL)|UPDATE]], and [[Delete (SQL)|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 [[Database trigger|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 system]]s have extended the views from read-only subsets of [[data]].

The [[Oracle database]] introduced the concept of [[materialized view]]s, 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. The equivalent of this in [[Microsoft SQL Server]], introduced in the 2000 version, is an indexed view. DB2 provides so-called ''materialized query tables (MQTs)'' for the same purpose.

==External links==