CREATE VIEW (Transact-SQL)CREATE VIEW (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

创建虚拟表,其内容(列和行)由查询定义。Creates a virtual table whose contents (columns and rows) are defined by a query. 使用此语句可以创建数据库中一个或多个表中数据的视图。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.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]   
[ WITH <view_attribute> [ ,...n ] ]   
AS select_statement   
[ WITH CHECK OPTION ]   
[ ; ]  
  
<view_attribute> ::=   
{  
    [ ENCRYPTION ]  
    [ SCHEMABINDING ]  
    [ VIEW_METADATA ]       
}   
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
CREATE VIEW [ schema_name . ] view_name [  ( column_name [ ,...n ] ) ]   
AS <select_statement>   
[;]  
  
<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT <select_criteria>  

参数Arguments

OR ALTEROR ALTER
适用范围:Azure SQL DatabaseAzure SQL DatabaseSQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 开始) 。Applies to: Azure SQL DatabaseAzure SQL Database and SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).

有条件地删除视图(仅当其已存在时)。Conditionally alters the view only if it already exists.

schema_nameschema_name
视图所属架构的名称。Is the name of the schema to which the view belongs.

view_name view_name
视图名称。Is the name of the view. 视图名称必须符合有关标识符的规则。View names must follow the rules for identifiers. 可以选择是否指定视图所有者名称。Specifying the view owner name is optional.

columncolumn
视图中的列使用的名称。Is the name to be used for a column in a view. 仅在下列情况下需要列名:列是从算术表达式、函数或常量派生的;两个或更多的列可能会具有相同的名称(通常是由于联接的原因);视图中的某个列的指定名称不同于其派生来源列的名称。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. 还可以在 SELECT 语句中分配列名。Column names can also be assigned in the SELECT statement.

如果未指定 column,则视图列将获得与 SELECT 语句中的列相同的名称 。If column is not specified, the view columns acquire the same names as the columns in the SELECT statement.

备注

在视图的各列中,列名的权限在 CREATE VIEW 或 ALTER VIEW 语句间均适用,与基础数据源无关。In the columns for the view, the permissions for a column name apply across a CREATE VIEW or ALTER VIEW statement, regardless of the source of the underlying data. 例如,如果在 CREATE VIEW 语句中授予了 SalesOrderID 列上的权限,则 ALTER VIEW 语句可以将 SalesOrderID 列改名(例如改为 OrderRef),但仍具有与使用 SalesOrderID 的视图相关联的权限 。For example, if permissions are granted on the SalesOrderID column in a CREATE VIEW statement, an ALTER VIEW statement can name the SalesOrderID column with a different column name, such as OrderRef, and still have the permissions associated with the view using SalesOrderID.

ASAS
指定视图要执行的操作。Specifies the actions the view is to perform.

select_statement select_statement
定义视图的 SELECT 语句。Is the SELECT statement that defines the view. 该语句可以使用多个表和其他视图。The statement can use more than one table and other views. 需要相应的权限才能在已创建视图的 SELECT 子句引用的对象中选择。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. 可以使用多个表或带任意复杂性的 SELECT 子句的其他视图创建视图。A view can be created that uses more than one table or other views with a SELECT clause of any complexity.

在索引视图定义中,SELECT 语句必须是单个表的语句或带有可选聚合的多表 JOIN。In an indexed view definition, the SELECT statement must be a single table statement or a multitable JOIN with optional aggregation.

视图定义中的 SELECT 子句不能包括下列内容:The SELECT clauses in a view definition cannot include the following:

  • ORDER BY 子句,除非在 SELECT 语句的选择列表中也有一个 TOP 子句。An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement

    重要

    ORDER BY 子句仅用于确定视图定义中的 TOP 或 OFFSET 子句返回的行。The ORDER BY clause is used only to determine the rows that are returned by the TOP or OFFSET clause in the view definition. ORDER BY 不保证在查询视图时得到有序结果,除非在查询本身中也指定了 ORDER BY。The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

  • INTO 关键字The INTO keyword

  • OPTION 子句The OPTION clause

  • 引用临时表或表变量。A reference to a temporary table or a table variable.

因为 select_statement 使用 SELECT 语句,所以按照 FROM 子句的指定,使用 <join_hint> 和 <table_hint> 提示是有效的 。Because select_statement uses the SELECT statement, it is valid to use <join_hint> and <table_hint> hints as specified in the FROM clause. 有关详细信息,请参阅 FROM (Transact-SQL)SELECT (Transact-SQL)For more information, see FROM (Transact-SQL) and SELECT (Transact-SQL).

UNION 或 UNION ALL 分隔的函数和多个 SELECT 语句可在 select_statement 中使用 。Functions and multiple SELECT statements separated by UNION or UNION ALL can be used in select_statement.

CHECK OPTIONCHECK OPTION
要求对该视图执行的所有数据修改语句都必须符合 select_statement 中所设置的条件 。Forces all data modification statements executed against the view to follow the criteria set within select_statement. 通过视图修改行时,WITH CHECK OPTION 可确保提交修改后,仍可通过视图看到数据。When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

备注

即使指定了 CHECK OPTION,也不能依据视图来验证任何直接对视图的基础表执行的更新。Any updates performed directly to a view's underlying tables are not verified against the view, even if CHECK OPTION is specified.

ENCRYPTIONENCRYPTION
适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

sys.syscomments 表中包含 CREATE VIEW 语句文本的项进行加密。Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. 使用 WITH ENCRYPTION 可防止在 SQL Server 复制过程中发布视图。Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

SCHEMABINDINGSCHEMABINDING
将视图绑定到基础表的架构。Binds the view to the schema of the underlying table or tables. 如果指定了 SCHEMABINDING,则不能按照将影响视图定义的方式修改基表或表。When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. 必须首先修改或删除视图定义本身,才能删除将要修改的表的依赖关系。The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. 使用 SCHEMABINDING 时,select_statement 必须包含所引用的表、视图或用户定义函数的两部分名称 (schema.object) 。When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. 所有被引用对象都必须在同一个数据库内。All referenced objects must be in the same database.

不能删除参与了使用 SCHEMABINDING 子句创建的视图的视图或表,除非该视图已被删除或更改而不再具有架构绑定。Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. 否则,数据库引擎Database Engine将引发错误。Otherwise, the 数据库引擎Database Engine raises an error. 另外,如果对参与具有架构绑定的视图的表执行 ALTER TABLE 语句,而这些语句又会影响视图定义,则这些语句将会失败。Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

VIEW_METADATAVIEW_METADATA
指定为引用视图的查询请求浏览模式的元数据时,SQL ServerSQL Server 实例将向 DB-Library、ODBC 和 OLE DB API 返回有关视图的元数据信息,而不返回基表的元数据信息。Specifies that the instance of SQL ServerSQL Server will return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. 浏览模式元数据是 SQL ServerSQL Server 实例向这些客户端 API 返回的附加元数据。Browse-mode metadata is additional metadata that the instance of SQL ServerSQL Server returns to these client-side APIs. 如果使用此元数据,客户端 API 将可以实现可更新客户端游标。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.

对于使用 VIEW_METADATA 创建的视图,浏览模式的元数据在描述结果集内视图中的列时,将返回视图名,而不返回基表名。For views created with VIEW_METADATA, the browse-mode metadata returns the view name and not the base table names when it describes columns from the view in the result set.

当使用 WITH VIEW_METADATA 创建视图时,如果该视图具有 INSTEAD OF INSERT 或 INSTEAD OF UPDATE 触发器,则视图的所有列(timestamp 列除外)都可更新 。When a view is created by using WITH VIEW_METADATA, all its columns, except a timestamp column, are updatable if the view has INSTEAD OF INSERT or INSTEAD OF UPDATE triggers. 有关可更新视图的详细信息,请参阅“备注”。For more information about updatable views, see Remarks.

RemarksRemarks

只能在当前数据库中创建视图。A view can be created only in the current database. CREATE VIEW 必须是查询批处理中的第一条语句。The CREATE VIEW must be the first statement in a query batch. 视图最多可以包含 1,024 列。A view can have a maximum of 1,024 columns.

通过视图进行查询时,数据库引擎Database Engine将进行检查以确保语句中任何位置被引用所有数据库对象都存在,这些对象在语句的上下文中有效,以及数据修改语句没有违反任何数据完整性规则。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.

如果某个视图依赖于已删除的表(或视图),则当有人试图使用该视图时,数据库引擎Database Engine将产生错误消息。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.

如果未使用 SCHEMABINDING 子句创建视图,请在对视图下影响视图定义的对象进行更改时,运行 sp_refreshviewIf a view is not created with the SCHEMABINDING clause, run sp_refreshview when changes are made to the objects underlying the view that affect the definition of the view. 否则,当查询视图时,可能会生成意外结果。Otherwise, the view might produce unexpected results when it is queried.

创建视图时,有关该视图的信息将存储在下列目录视图中:sys.viewssys.columnssys.sql_expression_dependenciesWhen a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies. CREATE VIEW 语句的文本将存储在 sys.sql_modules 目录视图中。The text of the CREATE VIEW statement is stored in the sys.sql_modules catalog view.

对通过 numeric 或 float 表达式定义的视图使用索引所得到的查询结果,可能不同于不对视图使用索引的类似查询所得到的结果 。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. 这种差异可能是由对基础表进行 INSERT、DELETE 或 UPDATE 操作时的舍入错误引起的。This difference may be caused by rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.

创建视图时,数据库引擎Database Engine将保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。The 数据库引擎Database Engine saves the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a view is created. 使用视图时,将使用这些原始设置来分析视图。These original settings are used to parse the view when the view is used. 因此,访问视图时,SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的任何客户端会话设置都不会影响视图定义。Therefore, any client-session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS do not affect the view definition when the view is accessed.

可更新的视图Updatable Views

只要满足下列条件,即可通过视图修改基础基表的数据:You can modify the data of an underlying base table through a view, as long as the following conditions are true:

  • 任何修改(包括 UPDATE、INSERT 和 DELETE 语句)都只能引用一个基表的列。Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

  • 视图中被修改的列必须直接引用表列中的基础数据。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:

    • 聚合函数:AVG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、VAR 和 VARP。An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.

    • 计算。A computation. 不能从使用其他列的表达式中计算该列。The column cannot be computed from an expression that uses other columns. 使用集合运算符 UNION、UNION ALL、CROSSJOIN、EXCEPT 和 INTERSECT 形成的列将计入计算结果,且不可更新。Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.

  • 被修改的列不受 GROUP BY、HAVING 或 DISTINCT 子句的影响。The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.

  • TOP 在视图的 select_statement 中的任何位置都不会与 WITH CHECK OPTION 子句一起使用 。TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

上述限制应用于视图的 FROM 子句中的任何子查询,就像其应用于视图本身一样。The previous restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. 通常情况下,数据库引擎Database Engine必须能够明确跟踪从视图定义到一个基表的修改。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:

  • INSTEAD OF 触发器INSTEAD OF Triggers

    可以对视图创建 INSTEAD OF 触发器,以使视图可更新。INSTEAD OF triggers can be created on a view to make a view updatable. 将执行 INSTEAD OF 触发器,而不是执行对其定义了触发器的数据修改语句。The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. 此触发器允许用户指定必须发生以处理数据修改语句的操作集合。This trigger lets the user specify the set of actions that must happen to process the data modification statement. 因此,如果存在给定的数据修改语句(INSERT、UPDATE 或 DELETE)的视图的 INSTEAD OF 触发器,则可通过该语句更新相应的视图。Therefore, if an INSTEAD OF trigger exists for a view on a specific data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement. 有关 INSTEAD OF 触发器的详细信息,请参阅 DML 触发器For more information about INSTEAD OF triggers, see DML Triggers.

  • 分区视图Partitioned Views

    如果视图为分区视图,则可遵循某些限制对其进行更新。If the view is a partitioned view, the view is updatable, subject to certain restrictions. 必要时,数据库引擎Database Engine将本地分区视图辨别为所有参与表和视图都在同一 SQL ServerSQL Server 实例上的视图,而将分布式分区视图辨别为视图中至少有一个表驻留在其他或远程服务器上的视图。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 ServerSQL 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.

分区视图Partitioned Views

分区视图是通过对成员表使用 UNION ALL 所定义的视图,这些成员表的结构相同,但作为多个表分别存储在同一个 SQL ServerSQL Server 实例中,或存储在称为联合数据库服务器的自主 SQL ServerSQL Server 服务器实例组中。A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL ServerSQL Server or in a group of autonomous instances of SQL ServerSQL Server servers, called federated database servers.

备注

对一个服务器的本地数据进行分区的首选方法是通过分区表。The preferred method for partitioning data local to one server is through partitioned tables. 有关详细信息,请参阅 Partitioned Tables and IndexesFor more information, see Partitioned Tables and Indexes.

在设计分区方案时,必须明确每个分区上包含的数据。In designing a partitioning scheme, it must be clear what data belongs to each partition. 例如,Customers 表的数据分布在三个服务器位置的三个成员表中:Customers_33 上的 Server1Customers_66 上的 Server2Customers_99 上的 Server3For example, the data for the Customers table is distributed in three member tables in three server locations: Customers_33 on Server1, Customers_66 on Server2, and Customers_99 on Server3.

Server1 的分区视图通过以下方式进行定义:A partitioned view on Server1 is defined in the following way:

--Partitioned view as defined on Server1  
CREATE VIEW Customers  
AS  
--Select from local member table.  
SELECT *  
FROM CompanyData.dbo.Customers_33  
UNION ALL  
--Select from member table on Server2.  
SELECT *  
FROM Server2.CompanyData.dbo.Customers_66  
UNION ALL  
--Select from member table on Server3.  
SELECT *  
FROM Server3.CompanyData.dbo.Customers_99;  

一般情况下,如果视图为下列格式,则称其为分区视图:Generally, a view is said to be a partitioned view if it is of the following form:

SELECT <select_list1>  
FROM T1  
UNION ALL  
SELECT <select_list2>  
FROM T2  
UNION ALL  
...  
SELECT <select_listn>  
FROM Tn;  

创建分区视图的条件Conditions for Creating Partitioned Views

  1. 选择 listThe select list

    • 在视图定义的列列表中,选择成员表中的所有列。In the column list of the view definition, select all columns in the member tables.

    • 确保每个 select list 中的同一序号位置上的列属于同一类型,包括排序规则。Ensure that the columns in the same ordinal position of each select list are of the same type, including collations. 列仅仅属于可隐式转换的类型(如通常情况下的 UNION)是不够的。It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.

      此外,至少有一列(例如 <col>)必须按照相同的序号位置显示在所有选择列表中。Also, at least one column (for example <col>) must appear in all the select lists in the same ordinal position. 按照以下方式定义 <col>:成员表 T1, ..., Tn 分别在 C1, ..., Cn 上定义 CHECK 约束 <col>Define <col> in a way that the member tables T1, ..., Tn have CHECK constraints C1, ..., Cn defined on <col>, respectively.

      在表 C1 上定义的约束 T1 必须是以下格式:Constraint C1 defined on table T1 must be of the following form:

      C1 ::= < simple_interval > [ OR < simple_interval > OR ...]  
      < simple_interval > :: =   
      < col > { < | > | \<= | >= | = < value >}   
      | < col > BETWEEN < value1 > AND < value2 >  
      | < col > IN ( value_list )  
      | < col > { > | >= } < value1 > AND  
      < col > { < | <= } < value2 >  
      
    • 约束必须按照以下方式定义:<col> 的任何指定值最多只能满足一个 C1, ..., Cn 约束,从而使约束形成一组不联接或不重叠的间隔。The constraints must be in such a way that any specified value of <col> can satisfy, at most, one of the constraints C1, ..., Cn so that the constraints form a set of disjointed or nonoverlapping intervals. 定义不联接的约束的列 <col> 称为分区列。The column <col> on which the disjointed constraints are defined is called the partitioning column. 请注意,分区列在基础表中可能有不同的名称。Note that the partitioning column may have different names in the underlying tables. 约束必须处于启用和信任状态,以使它们满足分区依据列的上述条件。The constraints must be in an enabled and trusted state for them to meet the previously mentioned conditions of the partitioning column. 如果约束被禁用,则使用 ALTER TABLE 的 CHECK CONSTRAINT constraint_name 选项重新启用约束检查,并使用 WITH CHECK 选项对其进行验证 。If the constraints are disabled, re-enable constraint checking by using the CHECK CONSTRAINT constraint_name option of ALTER TABLE, and using the WITH CHECK option to validate them.

      以下示例显示有效的约束集合:The following examples show valid sets of constraints:

      { [col < 10], [col between 11 and 20] , [col > 20] }  
      { [col between 11 and 20], [col between 21 and 30], [col between 31 and 100] }  
      
    • 在选择列表中不能多次使用同一列。The same column cannot be used multiple times in the select list.

  2. 分区列Partitioning column

    • 分区列是表的 PRIMARY KEY 的一部分。The partitioning column is a part of the PRIMARY KEY of the table.

    • 分区列不能是计算列、标识列、默认列或 timestamp 列 。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, ensure that there is only one partitioning constraint on the partitioning column.

    • 分区列的可更新性没有限制。There are no restrictions on the updatability of the partitioning column.

  3. 成员表或基础表 T1, ..., TnMember tables, or underlying tables T1, ..., Tn

    • 表可以是本地表,也可以是通过由四部分组成的名称或基于 OPENDATASOURCE 或 OPENROWSET 的名称引用的运行 SQL ServerSQL Server 的其他计算机中的表。The tables can be either local tables or tables from other computers that are running SQL ServerSQL Server that are referenced either through a four-part name or an OPENDATASOURCE- or OPENROWSET-based name. OPENDATASOURCE 和 OPENROWSET 语法可以指定表名,但不能指定直接传递查询。The OPENDATASOURCE and OPENROWSET syntax can specify a table name, but not a pass-through query. 有关详细信息,请参阅 OPENDATASOURCE (Transact-SQL)OPENROWSET (Transact-SQL)For more information, see OPENDATASOURCE (Transact-SQL) and OPENROWSET (Transact-SQL).

      如果一个或多个成员表是远程表,则视图将被称为“分布式分区视图”,并且将应用附加条件。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.

    • 在用 UNION ALL 语句组合的表集合中,同一个表不能出现两次。The same table cannot appear two times in the set of tables that are being combined with the UNION ALL statement.

    • 成员表不能对表中的计算列创建索引。The member tables cannot have indexes created on computed columns in the table.

    • 成员表在编号相同的列上具有所有 PRIMARY KEY 约束。The member tables have all PRIMARY KEY constraints on the same number of columns.

    • 视图中的所有成员表都具有相同的 ANSI 填充设置。All member tables in the view have the same ANSI padding setting. 这可以使用 sp_configure 中的 user options 选项或 SET 语句进行设置 。This can be set by using either the user options option in sp_configure or the SET statement.

在分区视图中修改数据的条件Conditions for Modifying Data in Partitioned Views

下面的限制适用于在分区视图中修改数据的语句:The following restrictions apply to statements that modify data in partitioned views:

  • 即使基础成员表对这些列具有 DEFAULT 约束或允许 NULL 值,INSERT 语句也会为视图中的所有列提供值。The INSERT statement supplies values for all the columns in the view, even if the underlying member tables have a DEFAULT constraint for those columns or if they allow for null values. 对于那些具有 DEFAULT 定义的成员表列,这些语句无法显式地使用关键字 DEFAULT。For those member table columns that have DEFAULT definitions, the statements cannot explicitly use the keyword DEFAULT.

  • 插入到分区依据列中的值至少满足一个基础约束;否则,插入操作将因约束冲突而失败。The value being inserted into the partitioning column satisfies at least one of the underlying constraints; otherwise, the insert action will fail with a constraint violation.

  • 即使列中包含在相应成员表中定义的 DEFAULT 值,UPDATE 语句也不能指定 DEFAULT 关键字作为 SET 子句中的值。UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause, even if the column has a DEFAULT value defined in the corresponding member table.

  • 不能通过 INSERT 或 UPDATE 语句修改视图中作为一个或多个成员表中的标识列的列。Columns in the view that are an identity column in one or more of the member tables cannot be modified by using an INSERT or UPDATE statement.

  • 如果其中的一个成员表包含 timestamp 列,则无法使用 INSERT 或 UPDATE 语句来修改数据 。If one of the member tables contains a timestamp column, the data cannot be modified by using an INSERT or UPDATE statement.

  • 如果一个成员表包含触发器或 ON UPDATE CASCADE/SET NULL/SET DEFAULT 或 ON DELETE CASCADE/SET NULL/SET DEFAULT 约束,则不能修改视图。If one of the member tables contains a trigger or an ON UPDATE CASCADE/SET NULL/SET DEFAULT or ON DELETE CASCADE/SET NULL/SET DEFAULT constraint, the view cannot be modified.

  • 如果语句中存在与相同视图或任何成员表的自联接,则不允许对分区视图使用 INSERT、UPDATE 和 DELETE 操作。INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.

  • 以下语句不支持将数据批量导入分区视图:bcp 或 BULK INSERT 和 INSERT ... SELECT * FROM OPENROWSET(BULK...) 语句使用格式化文件大容量导入数据。Bulk importing data into a partitioned view is unsupported by bcp or the BULK INSERT and INSERT ... SELECT * FROM OPENROWSET(BULK...) statements. 但是,可以使用 INSERT 语句在分区视图中插入多行。However, you can insert multiple rows into a partitioned view by using the INSERT statement.

    备注

    若要更新分区视图,用户必须具有对成员表的 INSERT、UPDATE 和 DELETE 权限。To update a partitioned view, the user must have INSERT, UPDATE, and DELETE permissions on the member tables.

分布式分区视图的附加条件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.

  • 将 XACT_ABORT SET 选项设置为 ON,以使 INSERT、UPDATE 或 DELETE 语句生效。Set the XACT_ABORT SET option to ON for INSERT, UPDATE, or DELETE statements to work.

  • 在分区视图中引用的远程表的所有 smallmoney 类型的列都将映射为 money 。Any columns in remote tables of type smallmoney that are referenced in a partitioned view are mapped as money. 因此,本地表中相应的列(在选择列表中的相同序号位置中)必须也为 money 类型 。Therefore, the corresponding columns (in the same ordinal position in the select list) in the local tables must also be of type money.

  • 数据库兼容级别为 110 和更高级别时,在分区视图中引用的远程表的所有 smalldatetime 类型的列都将映射为 smalldatetime 。Under database compatibility level 110 and higher, any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as smalldatetime. 本地表中相应的列(在选择列表中的相同序号位置中)必须为 smalldatetime 。Corresponding columns (in the same ordinal position in the select list) in the local tables must be smalldatetime. 这是对早期版本的 SQL ServerSQL Server 的行为的更改,在早期版本中,在分区视图中引用的远程表的所有 smalldatetime 类型的列都将映射为 datetime,并且本地表中相应的列必须为 datetime 类型 。This is a change in behavior from earlier versions of SQL ServerSQL 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. 有关详细信息,请参阅 ALTER DATABASE 兼容级别 (Transact-SQL)For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

  • 分区视图中的所有链接服务器都不能是环回链接服务器。Any linked server in the partitioned view cannot be a loopback linked server. 这是一个指向同一个 SQL ServerSQL Server 实例的链接服务器。This is a linked server that points to the same instance of SQL ServerSQL Server.

对于涉及可更新分区视图和远程表的 INSERT、UPDATE 和 DELETE 操作,忽略 SET ROWCOUNT 选项的设置。The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE actions that involve updatable partitioned views and remote tables.

设置了成员表和分区视图的定义后,SQL ServerSQL Server 查询优化器将生成智能计划,从而有效利用查询访问成员表中的数据。When the member tables and partitioned view definition are in place, the SQL ServerSQL Server query optimizer builds intelligent plans that use queries efficiently to access data from member tables. 通过使用 CHECK 约束定义,查询处理器在成员表间映射键值的分布。With the CHECK constraint definitions, the query processor maps the distribution of key values across the member tables. 用户发出查询时,查询处理器将映射与 WHERE 子句中指定的值进行比较,然后生成使成员服务器间的数据传输量减到最少的执行计划。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. 因此,虽然有些成员表可能位于远程服务器中,但是 SQL ServerSQL Server 将解析分布式查询,使得必须传输的分布式数据量减到最少。Therefore, although some member tables may be located in remote servers, the instance of SQL ServerSQL 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:

  • 如果基础表涉及到包含更新订阅的合并复制或事务复制,请确保 uniqueidentifier 列也包含在选择列表中 。If the underlying tables are involved in merge replication or transactional replication with updating subscriptions, ensure that the uniqueidentifier column is also included in the select list.

    对分区视图进行的任何 INSERT 操作都必须为 uniqueidentifier 列提供 NEWID() 值 。Any INSERT actions into the partitioned view must provide a NEWID() value for the uniqueidentifier column. 因为无法使用 DEFAULT 关键字,所以对 uniqueidentifier 列的任何 UPDATE 操作都必须提供 NEWID() 值 。Any UPDATE actions against the uniqueidentifier column must supply NEWID() as the value because the DEFAULT keyword cannot be used.

  • 通过视图进行的更新复制与在两个不同的数据库中复制表时相同:表由不同的复制代理进行处理,因此不能保证更新的顺序。The replication of updates made by using the view is the same as when tables are replicated in two different databases: the tables are served by different replication agents and the order of the updates is not guaranteed.

权限Permissions

要求在数据库中具有 CREATE VIEW 权限,并具有在其中创建视图的架构的 ALTER 权限。Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

示例Examples

下列示例使用 AdventureWorks 2012 和 AdventureWorksDW 数据库。The following examples use the AdventureWorks 2012 or AdventureWorksDW database.

A.A. 使用简单 CREATE VIEWUsing a simple CREATE VIEW

以下示例使用简单 SELECT 语句创建视图。The following example creates a view by using a simple SELECT statement. 当需要频繁地查询列的某种组合时,简单视图非常有用。A simple view is helpful when a combination of columns is queried frequently. 此视图的数据来自 AdventureWorks2012AdventureWorks2012 数据库的 HumanResources.EmployeePerson.Person 表。The data from this view comes from the HumanResources.Employee and Person.Person tables of the AdventureWorks2012AdventureWorks2012 database. 这些数据提供有关 Adventure Works CyclesAdventure Works Cycles 的雇员的姓名和雇用日期信息。The data provides name and hire date information for the employees of Adventure Works CyclesAdventure 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.

CREATE VIEW hiredate_view  
AS   
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate  
FROM HumanResources.Employee e   
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;  
GO  
  

B.B. 使用 WITH ENCRYPTIONUsing WITH ENCRYPTION

以下示例使用 WITH ENCRYPTION 选项,并显示计算列、重命名列以及多个列。The following example uses the WITH ENCRYPTION option and shows computed columns, renamed columns, and multiple columns.

适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and SQL 数据库SQL Database.

CREATE VIEW Purchasing.PurchaseOrderReject  
WITH ENCRYPTION  
AS  
SELECT PurchaseOrderID, ReceivedQty, RejectedQty,   
    RejectedQty / ReceivedQty AS RejectRatio, DueDate  
FROM Purchasing.PurchaseOrderDetail  
WHERE RejectedQty / ReceivedQty > 0  
AND DueDate > CONVERT(DATETIME,'20010630',101) ;  
GO  
  

C.C. 使用 WITH CHECK OPTIONUsing WITH CHECK OPTION

以下示例显示名为 SeattleOnly 的视图,此视图引用了五个表,并允许进行数据修改,以便仅适用于居住在西雅图的雇员。The following example shows a view named SeattleOnly that references five tables and allows for data modifications to apply only to employees who live in Seattle.

CREATE VIEW dbo.SeattleOnly  
AS  
SELECT p.LastName, p.FirstName, e.JobTitle, a.City, sp.StateProvinceCode  
FROM HumanResources.Employee e  
INNER JOIN Person.Person p  
ON p.BusinessEntityID = e.BusinessEntityID  
    INNER JOIN Person.BusinessEntityAddress bea   
    ON bea.BusinessEntityID = e.BusinessEntityID   
    INNER JOIN Person.Address a   
    ON a.AddressID = bea.AddressID  
    INNER JOIN Person.StateProvince sp   
    ON sp.StateProvinceID = a.StateProvinceID  
WHERE a.City = 'Seattle'  
WITH CHECK OPTION ;  
GO  

D.D. 使用视图中的内置函数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.

CREATE VIEW Sales.SalesPersonPerform  
AS  
SELECT TOP (100) SalesPersonID, SUM(TotalDue) AS TotalSales  
FROM Sales.SalesOrderHeader  
WHERE OrderDate > CONVERT(DATETIME,'20001231',101)  
GROUP BY SalesPersonID;  
GO  

E.E. 使用分区数据Using partitioned data

以下示例将使用名称分别为 SUPPLY1SUPPLY2SUPPLY3SUPPLY4 的表。The following example uses tables named SUPPLY1, SUPPLY2, SUPPLY3, and SUPPLY4. 这些表对应于位于四个国家/地区的四个办事处的供应商表。These tables correspond to the supplier tables from four offices, located in different countries/regions.

--Create the tables and insert the values.  
CREATE TABLE dbo.SUPPLY1 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),  
supplier CHAR(50)  
);  
CREATE TABLE dbo.SUPPLY2 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),  
supplier CHAR(50)  
);  
CREATE TABLE dbo.SUPPLY3 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),  
supplier CHAR(50)  
);  
CREATE TABLE dbo.SUPPLY4 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),  
supplier CHAR(50)  
);  
GO  
--Create the view that combines all supplier tables.  
CREATE VIEW dbo.all_supplier_view  
WITH SCHEMABINDING  
AS  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY1  
UNION ALL  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY2  
UNION ALL  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY3  
UNION ALL  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY4;  
GO
INSERT dbo.all_supplier_view VALUES ('1', 'CaliforniaCorp'), ('5', 'BraziliaLtd')    
, ('231', 'FarEast'), ('280', 'NZ')  
, ('321', 'EuroGroup'), ('442', 'UKArchip')  
, ('475', 'India'), ('521', 'Afrique');  
GO  

示例:SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: SQL 数据仓库SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

F.F. 创建简单视图Creating a simple view

下面的示例通过仅从源表选择某些列来创建视图。The following example creates a view by selecting only some of the columns from the source table.

CREATE VIEW DimEmployeeBirthDates AS  
SELECT FirstName, LastName, BirthDate   
FROM DimEmployee;  

G.G. 通过联接两个表来创建视图Create a view by joining two tables

以下示例通过搭配使用 SELECT 语句和 OUTER JOIN 来创建视图。The following example creates a view by using a SELECT statement with an OUTER JOIN. 联接查询的结果将会填充视图。The results of the join query populate the view.

CREATE VIEW view1  
AS 
SELECT fis.CustomerKey, fis.ProductKey, fis.OrderDateKey, 
  fis.SalesTerritoryKey, dst.SalesTerritoryRegion  
FROM FactInternetSales AS fis   
LEFT OUTER JOIN DimSalesTerritory AS dst   
ON (fis.SalesTerritoryKey=dst.SalesTerritoryKey);  

另请参阅See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
ALTER VIEW (Transact-SQL) ALTER VIEW (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
DROP VIEW (Transact-SQL) DROP VIEW (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
创建存储过程 Create a Stored Procedure
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL) sys.dm_sql_referencing_entities (Transact-SQL)
sp_help (Transact-SQL) sp_help (Transact-SQL)
sp_helptext (Transact-SQL) sp_helptext (Transact-SQL)
sp_refreshview (Transact-SQL) sp_refreshview (Transact-SQL)
sp_rename (Transact-SQL) sp_rename (Transact-SQL)
sys.views (Transact-SQL) sys.views (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
EVENTDATA (Transact-SQL)EVENTDATA (Transact-SQL)