Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes: To focus, simplify, and customize the perception each user has of the database. As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
To provide a backward compatible interface to emulate a table whose schema has changed. Conditionally alters the view only if it already exists. View names must follow the rules for identifiers. Specifying the view owner name is optional. A column name is required only when a column is derived from an arithmetic expression, a function, or a constant; when two or more columns may otherwise have the same name, typically because of a join; or when a column in a view is specified a name different from that of the column from which it is derived.
AS Specifies the actions the view is to perform. The statement can use more than one table and other views. Appropriate permissions are required to select from the objects referenced in the SELECT clause of the view that is created. A view does not have to be a simple subset of the rows and columns of one particular table. Encrypts the entries in sys. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.
All referenced objects must be in the same database. Otherwise, the Database Engine raises an error. This metadata enables the client-side APIs to implement updatable client-side cursors. Browse-mode metadata includes information about the base table that the columns in the result set belong to. For more information about updatable views, see Remarks. Remarks A view can be created only in the current database. A view can have a maximum of 1, columns. When querying through a view, the Database Engine checks to make sure that all the database objects referenced anywhere in the statement exist and 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 or tables. If a view depends on a table or view that was dropped, the Database Engine produces an error message when anyone tries to use the view. If a new table or view is created and the table structure does not change from the previous base table to replace the one dropped, the view again becomes usable.
If the new table or view structure changes, the view must be dropped and re-created. Otherwise, the view might produce unexpected results when it is queried. When a view is created, information about the view is stored in the following catalog views: A query that uses an index on a view defined with numeric or float expressions may have a result that is different from a similar query that does not use the index on the view. These original settings are used to parse the view when the view is used.
Updatable Views You can modify the data of an underlying base table through a view, as long as the following conditions are true: The columns being modified in the view must directly reference the underlying data in the table columns.
The columns cannot be derived in any other way, such as through the following: The column cannot be computed from an expression that uses other columns. The previous restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table.
For more information, see Modify Data Through a View. If the previous restrictions prevent you from modifying data directly through a view, consider the following options: This trigger lets the user specify the set of actions that must happen to process the data modification statement. Partitioned Views If the view is a partitioned view, the view is updatable, subject to certain restrictions.
When it is needed, the Database Engine distinguishes local partitioned views as the views in which all participating tables and the view are on the same instance of SQL Server, and distributed partitioned views as the views in which at least one of the tables in the view resides on a different or remote server. Note The preferred method for partitioning data local to one server is through partitioned tables. For more information, see Partitioned Tables and Indexes.
In designing a partitioning scheme, it must be clear what data belongs to each partition. For example, the data for the Customers table is distributed in three member tables in three server locations: A partitioned view on Server1 is defined in the following way: The columns in the same ordinal position of each select list should be of the same type, including collations.
It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION. Constraint C1 defined on table T1 must be of the following form: Note that the partitioning column may have different names in the underlying tables. The constraints should be in an enabled and trusted state for them to meet the previously mentioned conditions of the partitioning column.
The following examples show valid sets of constraints: It cannot be a computed, identity, default, or timestamp column. If there is more than one constraint on the same column in a member table, the Database Engine ignores all the constraints and does not consider them when determining whether the view is a partitioned view.
To meet the conditions of the partitioned view, there should be only one partitioning constraint on the partitioning column. There are no restrictions on the updatability of the partitioning column. Member tables, or underlying tables T1, If one or more of the member tables are remote, the view is called distributed partitioned view, and additional conditions apply. They are described later in this section.
The member tables cannot have indexes created on computed columns in the table. All member tables in the view should have the same ANSI padding setting. Conditions for Modifying Data in Partitioned Views The following restrictions apply to statements that modify data in partitioned views: The value being inserted into the partitioning column should satisfy at least one of the underlying constraints; otherwise, the insert action will fail with a constraint violation.
Additional Conditions for Distributed Partitioned Views For distributed partitioned views when one or more member tables are remote , the following additional conditions apply: A distributed transaction will be started to guarantee atomicity across all nodes affected by the update.
Any columns in remote tables of type smallmoney that are referenced in a partitioned view are mapped as money. Therefore, the corresponding columns in the same ordinal position in the select list in the local tables must also be of type money. Under database compatibility level and higher, any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as smalldatetime.
Corresponding columns in the same ordinal position in the select list in the local tables must be smalldatetime. This is a change in behavior from earlier versions of SQL Server in which any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as datetime and corresponding columns in local tables must be of type datetime. Any linked server in the partitioned view cannot be a loopback linked server. This is a linked server that points to the same instance of SQL Server.
When the member tables and partitioned view definition are in place, the SQL Server query optimizer builds intelligent plans that use queries efficiently to access data from member tables. With the CHECK constraint definitions, the query processor maps the distribution of key values across the member tables. When a user issues a query, the query processor compares the map to the values specified in the WHERE clause, and builds an execution plan with a minimal amount of data transfer between member servers.
Therefore, although some member tables may be located in remote servers, the instance of SQL Server resolves distributed queries so that the amount of distributed data that has to be transferred is minimal.
Considerations for Replication To create partitioned views on member tables that are involved in replication, the following considerations apply: If the underlying tables are involved in merge replication or transactional replication with updating subscriptions, the uniqueidentifier column should also be included in the select list.
The replication of updates made by using the view is the same as when tables are replicated in two different databases: A simple view is helpful when a combination of columns is queried frequently. The data from this view comes from the HumanResources. Person tables of the AdventureWorks database. The data provides name and hire date information for the employees of Adventure Works Cycles. The view could be created for the person in charge of tracking work anniversaries but without giving this person access to all the data in these tables.
Person AS p ON e. Person p ON p. BusinessEntityAddress bea ON bea. Address a ON a. StateProvince sp ON sp. Using built-in functions within a view The following example shows a view definition that includes a built-in function.
When you use functions, you must specify a column name for the derived column. Creating a simple view The following example creates a view by selecting only some of the columns from the source table. The results of the join query populate the view. SalesTerritoryKey ; See Also.