View (SQL): Difference between revisions

Revision as of 21:38, 12 December 2023

Revision as of 17:14, 13 December 2023

Line 18:

* Views can [[Join (SQL)|join]] and simplify multiple tables into a single virtual table.<ref name="SQL-reference-groff-weinberg" />

* Views can act as aggregated tables, where the [[database engine]] aggregates data ([[summation|sum]], [[average]], etc.) and presents the calculated results as part of the data.

* Views can hide the complexity of data. For example, a view could appear as Sales2000Sales2020 or Sales2001Sales2021, transparently [[Partition (database)|partitioning]] the actual underlying table.

* Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.

* Structures data in a way that classes of users find natural and intuitive.<ref name="SQL-reference-groff-weinberg"/>

Line 26:

== Read-only vs. updatable views ==

Views can be defined as [[file system permissions|read-only]] or updatable. If the database system can 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 cannot map the changes to the underlying base tables. A view update is done by key preservation.

Some systems support the definition of INSTEAD OF [[Database trigger|triggers]] on views. This technique allows the definition of other logic for execution in place of an insert, update, or delete operation on the views. Thus database systems can implement data modifications based on read-only views. However, an INSTEAD OF trigger does not change the read-only or updatable property of the view itself.