In Enterprise applications, we find that there are numerous tables and those may be normalized. So data to be inserted for a "Business Entity" may be split into various tables as part of normailzation. So to ensure that you include related tables into your select queries, views are a good medium.
What are viewsViews are nothing but a virtual table (note that a table is
not created for a view). The view will only have a SQL statement that will be executed in background once you query the view. It will have a similar structure like a table (rows, columns and so on).
The following is the syntax for creating views (in its simplest form):
- CREATE VIEW [ schema. ] view_name
- AS select_statement
Schema: Is the name of the schema to which the view belongs.
View_name: Is the name of the view.
AS: Specifies the actions the view is to perform.
Select_statement: Is the SELECT statement that defines the view. The statement can use more than one table and other views.
Updatable Views
Once you have created a view, we can use that view to update data in the underlying tables. Since views are just SQL select statements, there are a few restrictions on which views can update data back to the tables. The most logical that I can think of is that if you are projecting the result of some computation over a table column, how is the view supposed to revert that computation when trying to insert data back to the table? The complete list of restrictions are given below (reference:
MSDN).
An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP.
A computation. The column cannot be computed from an expression that uses other columns. Columns formed using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT and INTERSECT amount to a computation and are also not updatable.