Creates a virtual table that represents an alternative way of looking at the data in one or more tables. You can use views as security mechanisms by granting permission on a view but not on underlying tables.
CREATE VIEW [owner.]view_name
[(column_name [, column_name]...)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]
where
If column_name is not specified, the view columns acquire the same names as the columns in the SELECT statement.
Important When a database is upgraded, syscomments entries are required in order to re-create views. Use encryption only when absolutely necessary; never delete entries from syscomments.
A view need not be a simple subset of the rows and columns of one particular table. You can create a view using more than one table and/or other views with a SELECT clause of any complexity.
There are, however, a few restrictions on the SELECT clauses in a view definition:
You can create a view only in the current database. A view can reference a maximum of 250 columns.
In a view defined with a SELECT * clause, if you alter the structure of its underlying table(s) by adding columns, the new columns do not appear in the view unless the view is first deleted and redefined. The asterisk shorthand is interpreted and expanded when the view is first created.
When you query through a view, SQL Server checks to make sure that all the database objects referenced anywhere in the statement exist, that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules. A check that fails returns an error message. A successful check translates the action into an action against the underlying table(s).
If a view depends on a table (or view) that has been dropped, SQL Server produces an error message if anyone tries to use the view. If a new table (or view) is created to replace the one dropped, the view again becomes usable.
When you create a view, the name of the view is stored in the sysobjects table and the view's normalized query tree is stored in the sysprocedures table. Information about the columns defined in a view is added to the syscolumns table, and information about the view dependencies is added to the sysdepends table. In addition, the text of the view creation statement is added to the syscomments table. This is similar to a stored procedure; but when a view is executed for the first time, only its query tree is stored in procedure cache. Each time a view is accessed, its execution plan is recompiled.