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

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: YesSQL Server YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) 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:

  • 對焦 (Focus)、簡化和自訂每位使用者查看資料庫的角度。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 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_nameview_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_statementselect_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).

您可以在 select_statement 中使用由 UNION 或 UNION ALL 來分隔的函數和多個 SELECT 陳述式。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 2008 及更新版本和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 and later 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 EngineDatabase Engine 會引發錯誤。Otherwise, the Database EngineDatabase 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.

備註Remarks

檢視只能建立在目前資料庫中。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 EngineDatabase Engine 會檢查確定陳述式所參考的所有資料庫物件都存在、在陳述式的內容中有效,且修改資料陳述式未違反任何資料完整性規則。When querying through a view, the Database EngineDatabase 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 EngineDatabase Engine 一律會產生錯誤訊息。If a view depends on a table or view that was dropped, the Database EngineDatabase 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.

如果查詢使用的檢視索引是以 numericfloat 運算式所定義,其查詢結果可能會與未使用這份檢視索引的類似查詢不同。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 EngineDatabase Engine 會儲存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設定。The Database EngineDatabase 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.

  • 在檢視的 select_statement 中任何位置,都不能將 TOP 與 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 EngineDatabase Engine 必須能夠明確地追蹤從檢視定義到一份基底資料表的修改。Generally, the Database EngineDatabase 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. 因此,如果 INSTEAD OF 觸發程序是針對某份特定資料修改陳述式 (INSERT、UPDATE 或 DELETE) 的檢視而存在,您也可以利用這個陳述式來更新對應的檢視。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 EngineDatabase Engine 會將本機分割區檢視當做所有參與的資料表及檢視本身是在相同 SQL ServerSQL Server 執行個體的檢視,並將分散式分割區檢視當做檢視中至少有一份資料表是在不同伺服器或遠端伺服器的檢視。When it is needed, the Database EngineDatabase 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 Indexes>。For more information, see Partitioned Tables and Indexes.

在設計分割區結構描述時,您必須清楚哪些資料屬於哪個分割區。In designing a partitioning scheme, it must be clear what data belongs to each partition. 例如,Customers 資料表的資料散發在三個伺服器位置的三份成員資料表中:Customers_33Server1Customers_66Server2,以及 Customers_99Server3For 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. 透過讓成員資料表 T1, ..., Tn 分別在 <col> 上具有 CHECK 條件約束 C1, ..., Cn 的方式來定義 <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.

    • 如果成員資料表的相同資料行有多個條件約束,Database Engine 會忽略所有條件約束,當判斷檢視是否為分割區檢視時,並不會考量它們。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 或 SET 陳述式中的 user options 選項來設定這個項目。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:

  • INSERT 陳述式需提供檢視中所有資料行的值,即使基礎成員資料表有這些資料行的 DEFAULT 條件約束或允許 Null 值,也是如此。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.

  • UPDATE 陳述式不能指定 DEFAULT 關鍵字作為 SET 子句中的值,即使資料行有對應成員資料表所定義的 DEFAULT 值也是如此。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 類型之遠端資料表中的任何資料行,如果它們受到資料分割檢視所參考,則系統會將這些資料行對應為 moneyAny 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 類型之遠端資料表中的任何資料行,如果它們受到資料分割檢視所參考,則系統會將這些資料行對應為 smalldatetimeUnder 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. 本機資料表中對應的資料行 (在選取清單的相同序數位置中) 必須為 smalldatetimeCorresponding 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 2008 及更新版本和 SQL DatabaseSQL DatabaseApplies to: SQL Server 2008SQL Server 2008 and later and SQL DatabaseSQL 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 的檢視,這份檢視參考五份資料表,可讓資料修改只套用在居住在 Seattle 的員工。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)