Database Views| MSSQL Views| SQL Server Views| Syntax of Database Views| Types of Views

A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.

Syntax
CREATE VIEW [<> . ] view_name [ ( column [ ,...n ] ) ] [ WITH <> [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ]
<> ::= { ENCRYPTION SCHEMABINDING VIEW_METADATA }
Note: Column is for statements, which contain aggregate functions
Encryption: Code View is encrypted by using this.
Schemabinding: The view is binded with the underlying table structure. Suppose you drop a column of the base table, normally the view gives an error that there is no such column name But if you specify With Schemabinding, then we will not be able to drop the column as it is being accessed by other objects.
View_MetaData: Returns the meta data information about the view.
Check Option: Suppose we create a view with a query, which has where condition. For this view, if the user tries to insert the data that does not match the query then its of no use as that is not shown in the result. So that means, we should give access only to insert records which meets the query criteria. Hence WITH CHECK OPTION is used. The result would be an error for the situation above.
A CREATE VIEW statement cannot:
Include ORDER BY clause, unless there is also a TOP clause (remember that a view is nothing else but a virtual table, why would we want to order it?)
Include the INTO keyword.
Include COMPUTE or COMPUTE BY clauses.
Reference a temporary table or a table variable.
Select statement can use multiple SELECT statements separated by UNION or UNION ALL.
Restrictions for updating data:
A view cannot modify more than one table. So if a view is based on two or more tables, and you try to run a DELETE statement, it will fail. If you run an UPDATE or INSERT statement, all columns referenced in the statement must belong to the same table.
It’s not possible to update, insert or delete data in a view with a DISTINCT clause.
You cannot update, insert or delete data in a view that is using GROUP BY.
It’s not possible to update, insert or delete data in a view that contains calculated columns.

Types:
Indexed Views: Indexed views work best for queries that aggregate or compute columns in the table. The disadvantage of indexed views is that it will slow down a query that updates data in the underlying tables.
Partitioned Views: When you need to write a join query with tables that are in different databases, then we use partitioned views.

Tags: Database Views, SQL Views, MSSQL Views, SQL Server Views, Syntax of Database Views, Types of Views, Types of database views