Creating a View
Before you create a view, consider these guidelines:
- You can create views only in the current database. However, the tables and views referenced by the new view can exist in other databases or even other servers if the view is defined using distributed queries.
- View names must follow the rules for identifiers and must be unique for each user. Additionally, the name must not be the same as any tables owned by that user.
- You can build views on other views and on procedures that reference views. Microsoft® SQL Server™ 2000 allows views to be nested up to 32 levels.
- You cannot associate rules or DEFAULT definitions with views.
- You cannot associate AFTER triggers with views, only INSTEAD OF triggers.
- The query defining the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
- You cannot define full-text index definitions on views.
- You cannot create temporary views, and you cannot create views on temporary tables.
- Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped, unless the view is dropped or changed so that it no longer has schema binding. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.
- You cannot issue full-text queries against a view, although a view definition can include a full-text query if the query references a table that has been configured for full-text indexing.
- You must specify the name of every column in the view if:
- Any of the columns in the view is derived from an arithmetic expression, a built-in function, or a constant.
- Two or more of the columns in the view would otherwise have the same name (usually because the view definition includes a join and the columns from two or more different tables have the same name).
- You want to give any column in the view a name different from the column from which it is derived. (You can also rename columns in the view.) A view column inherits the data type of the column from which it is derived, whether or not you rename it.
Note This rule does not apply when a view is based on a query containing an outer join because columns may change from not allowing null values to allowing them.
Otherwise, you do not need to specify column names when creating the view. SQL Server gives the columns of the view the same names and data types as the columns to which the query defining the view refers. The select list can be a full or partial list of the column names in the base tables.
- Any of the columns in the view is derived from an arithmetic expression, a built-in function, or a constant.
To create a view you must be granted permission to do so by the database owner and you must have appropriate permissions on any tables or views referenced in the view definition.
By default, as rows are added or updated through a view, they disappear from the scope of the view when they no longer fall into the criteria of the query defining the view. For example, a query can be created, defining a view that retrieves all rows from a table where the employee's salary is less than $30,000. If the employee's salary is increased to $32,000, then querying the view no longer displays that particular employee because his or her salary does not conform to the criteria set by the view. However, the WITH CHECK OPTION clause forces all data modification statements executed against the view to adhere to the criteria set within the SELECT statement defining the view. If you use this clause, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.
The definition of a sensitive view can be encrypted to ensure that its definition cannot be obtained by anyone, including the owner of the view.
To create a view
Transact-SQLYou can also create a view using the SQL Server Enterprise Manager Create View Wizard.
To create a view using the Create View Wizard