View

Jump to: navigation, search

In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions. Unlike ordinary tables (base tables) in a relational database, a view does not form 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 subsequent invocations of the view. In some NoSQL 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
• Views can join and simplify multiple tables into a single virtual table
• Views can act as aggregated tables, where the database engine aggregates data (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 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 it presents
• Depending on the SQL engine used, views can provide extra security
• Views can limit the degree of exposure of a table or tables to the outer world

Just as functions (in programming) can provide abstraction, so database users can create abstraction by using views. 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 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 - the rows in a view are not ordered, either. Therefore, an ORDER BY clause in the view definition is meaningless. The SQL standard (SQL:2003) does not allow an ORDER BY clause in a subselect in a CREATE VIEW statement, just as it is not allowed 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. Nevertheless, some DBMS (such as Oracle and SQL Server) allow a view to be created with an ORDER BY clause in a subquery, affecting how data is displayed.

Add to Cart