Database view (normal view and materialized view) __ Database

(1) materialized view

SQL Server does not have the concept of materialized views, but instead uses indexed views instead (refer to: https://msdn.microsoft.com/zh-cn/library/ms191432.aspx and https:// msdn.microsoft.com/zh-cn/library/aa290257%28vs.71%29.aspx), the materialized view here refers mainly to materialized views in Oracle databases.

(2) Normal view and materialized view

Materialized views are a special physical table, and the materialized view is relative to a normal view. Normal view is a virtual table, the application of the limitations of a large, any view of the query, are actually converted to the query SQL statements. This improves overall query performance without substantially better (3) View Advantages

A. Focus
Views allow users to care only about the specific data that they are interested in and the specific tasks they are responsible for. This improves the security of the data by allowing only the user to see the data defined in the view instead of the view referencing the data in the table.
B. Simplifying operations
The view greatly simplifies the user's manipulation of the data. Because when you define a view, the view itself is the result set of a complex query, so that when you execute the same query every time, you do not have to write back these complex query statements, as long as you have a simple query view statement. The visible view hides the complex connection operations between the tables and the table from the user, and also simplifies the query operation.
C. Custom Data
Views enable different users to see different or identical sets of data in different ways. Therefore, this is extremely important when there are many different levels of users sharing the same database.
D. Merging split data
In some cases, because the amount of data in the table is too large, the table is often divided horizontally or vertically in the design of the table, but the change of the structure of the table has a bad effect on the application. If you use a view, you can retain the original structure relationship so that the external mode remains unchanged, and the existing application can still overload the data through the view. (3) The updatable nature of the view

If the view satisfies the following conditions, it is updatable: the form clause has only one database relational table, that is, a view from only one table query, not a view from two tables and above
The SELECT clause contains only the database relational table property name, does not contain any expressions, aggregate functions, or distinct, or the properties that do not appear after the SELECT clause in the Database relational table can be null (a null value), that is, these properties do not have not null about Bundles, or do not form part of the main code, because these constraints restrict attributes cannot be null and cannot be grouped queries, that is, no group by or having statements

It is easy to understand from the above that if a view is derived from multiple tables, it cannot be updated because once the update involves more than one table, which is not allowed by the view, it cannot be updated if the view contains complex queries, such as grouping or clustered queries. Because the views of these queries cannot be updated with the original relational table data, the third article emphasizes "properties that do not appear in the Database relational table after the SELECT clause" because the properties in the view are only part of the original relational table, and when the data is inserted, You can insert only the attributes that the view contains, and for the properties that are not included, the database can only be empty, so the attribute that the view does not contain is allowed to be empty.

Even if the view meets these conditions, there are various problems with updating the view in some cases. At the same time, different database systems specify different criteria to allow updating of the view relationship, please refer to the database manual for more information. In addition, for more than one table query view, there is also a way to update, you can use the method of the trigger to update, the specific update method here is not much introduction.

Even so, in all respects, it is not recommended that the view be updated, but rather that the original database relational table is updated.