HOME

TheInfoList



OR:

In a
database In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases ...
, a view is the result set of a ''stored'' query on the
data In the pursuit of knowledge, data (; ) is a collection of discrete values that convey information, describing quantity, quality, fact, statistics, other basic units of meaning, or simply sequences of symbols that may be further interpret ...
, which the
database In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases ...
users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary ''base tables'' in a
relational database A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relati ...
, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant ''underlying table'' are reflected in the data shown in subsequent invocations of the view. In some
NoSQL A NoSQL (originally referring to "non- SQL" or "non-relational") database provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases. Such databases have existed ...
databases, views are the only way to query data. Views can provide advantages over tables: * Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table. * Views can
join Join may refer to: * Join (law), to include additional counts or additional defendants on an indictment *In mathematics: ** Join (mathematics), a least upper bound of sets orders in lattice theory ** Join (topology), an operation combining two topo ...
and simplify multiple tables into a single virtual table. * Views can act as aggregated tables, where the
database engine A database engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database. Most database management systems include their own applicati ...
aggregates data ( sum,
average In ordinary language, an average is a single number taken as representative of a list of numbers, usually the sum of the numbers divided by how many numbers are in the list (the arithmetic mean). For example, the average of the numbers 2, 3, 4, 7 ...
, 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 Sales2000 or Sales2001, transparently 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. * Depending on the SQL engine used, views can provide extra security. Just as a function (in programming) can provide
abstraction Abstraction in its main sense is a conceptual process wherein general rules and concepts are derived from the usage and classification of specific examples, literal ("real" or " concrete") signifiers, first principles, or other methods. "An abst ...
, so can a database view. In another parallel with functions, database users can manipulate nested views, thus one view can aggregate data from other views. Without the use of views, the normalization of databases above
second normal form Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined by E. F. Codd in 1971.Codd, E. F. "Further Normalization of the Data Base Relational Model". (Presented at Courant Computer Science Symposia Se ...
would become much more difficult. Views can make it easier to create lossless join decomposition. Just as rows in a base table lack any defined ordering, rows available through a view do not appear with any default sorting. A view is a relational table, and the relational model defines a table as a set of rows. Since sets are not ordered — by definition — neither are the rows of a view. Therefore, an ORDER BY clause in the view definition is meaningless; the SQL standard ( SQL:2003) does not allow an ORDER BY clause in the subquery of a CREATE VIEW command, just as it is refused in a CREATE TABLE statement. However, sorted data can be obtained from a view, in the same way as any other table — as part of a query statement on that view. Nevertheless, some DBMS (such as
Oracle Database Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation. It is a database commonly used for running online ...
) do not abide by this SQL standard restriction.


Read-only vs. updatable views

Database practitioners can define views as 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 Insert may refer to: *Insert (advertising) *Insert (composites) *Insert (effects processing) *Insert (filmmaking) *Insert key on a computer keyboard, used to switch between insert mode and overtype mode *Insert (molecular biology) *Insert (SQL) *Fi ...
, UPDATE, and 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 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.


Materialized views

Various
database management system In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases ...
s have extended the views from read-only subsets of
data In the pursuit of knowledge, data (; ) is a collection of discrete values that convey information, describing quantity, quality, fact, statistics, other basic units of meaning, or simply sequences of symbols that may be further interpret ...
, particularly
materialized view 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 ...
s: pre-executed, non-virtual views commonly used in
data warehousing In computing, a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis and is considered a core component of business intelligence. DWs are central repositories of integr ...
. They give a static snapshot of the data and may include data from remote sources. The accuracy of a materialized view depends on the frequency of trigger mechanisms behind its updates. Materialized views were introduced by
Oracle Database Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation. It is a database commonly used for running online ...
, while
IBM Db2 Db2 is a family of data management products, including database servers, developed by IBM. It initially supported the relational model, but was extended to support object–relational features and non-relational structures like JSON a ...
provides so-called "materialized query tables" (MQTs) for the same purpose.
Microsoft SQL Server Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ...
introduced in its 2000 version indexed views which only store a separate index from the table, but not the entire data.
PostgreSQL PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the ...
implemented materialized views in its 9.3 release.


Equivalence

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 with the content as follows: -- 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.customer_id = c.customer_id then the application could run a simple query such as: -- Simple query ------------ SELECT name, balance FROM accounts_view The RDBMS then takes the simple query, replaces the equivalent view, then sends the following to the
query optimizer Query optimization is a feature of many relational database management systems and other databases such as NoSQL and graph databases. The query optimizer attempts to determine the most efficient way to execute a given query by considering the ...
: -- 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.customer_id = c.customer_id ) The optimizer then removes unnecessary fields and complexity (for example 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.


See also

*
Bidirectionalization In computer science, bidirectionalization refers to the process of given a source-to-view transformation (automatically) finding a mapping from the original source and an updated view to an updated source. See also * Bidirectional transformation ...


External links


Views in Microsoft SQL Server









Views in CouchDB

Views in NuoDB
{{DEFAULTSORT:View (Database) Database management systems SQL Database theory