CREATE VIEW Statement

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.

Syntax

CREATE VIEW [owner.]view_name
[(column_name [, column_name]...)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

where

view_name

Is the name of the view. View names must follow the rules for identifiers.

column_name

Is the name to be used for a column in a view. Naming a column in CREATE VIEW is always legal but only necessary when a column is derived from an arithmetic expression, a function, or a constant, when two or more columns could otherwise have the same name (usually because of a join), or when you want to give a column in a view a name different from the column from which it is derived. Column names can also be assigned in the SELECT statement.

If column_name is not specified, the view columns acquire the same names as the columns in the SELECT statement.

ENCRYPTION

Encrypts the syscomments entries that contain the text of the CREATE VIEW 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.

AS select_statement

Is the SELECT statement that defines the view. It can use more than one table and other views. You must have permission to select from the objects referenced in the SELECT clause of a view you are creating.

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 cannot include ORDER BY, COMPUTE, or COMPUTE BY clauses.
• You cannot include the INTO keyword.
• You cannot reference a temporary table.

WITH CHECK OPTION

Forces all data modification statements executed against the view to adhere to the criteria set within the select_statement defining the view. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data will remain visible through the view after the modification has been committed.

Remarks

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.