CREATE VIEW (Transact-SQL)

只要符合下列條件，您可以利用檢視來修改基礎基底資料表的資料：

• 包括 UPDATE、INSERT 和 DELETE 陳述式在內的任何修改都只能參考一份基底資料表的資料行。

• 檢視所修改的資料行必須直接參考資料表資料行中的基礎資料。您無法利用任何其他方法來衍生這些資料行，例如：

  • 彙總函式：AVG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、VAR 和 VARP。

  • 計算。您不能從使用其他資料行的運算式計算資料行。利用設定運算子 UNION、UNION ALL、CROSSJOIN、EXCEPT 和 INTERSECT 形成的資料行會得出一項計算，這些資料行無法更新。

• GROUP BY、HAVING 或 DISTINCT 子句不會影響所修改的資料行。

• 檢視的 select_statement 中任何位置都不會使用 TOP 與 WITH CHECK OPTION 子句。

先前的限制適用於檢視之 FROM 子句中的任何子查詢，正如同它們適用於檢視本身一樣。一般而言，Database Engine 必須能夠明確地追蹤從檢視定義到一份基底資料表的修改。如需詳細資訊，請參閱＜透過檢視修改資料＞。

如果先前的限制使您無法直接利用檢視來修改資料，請考量下列選項：

• INSTEAD OF 觸發程序

  您可以建立檢視的 INSTEAD OF 觸發程序，使檢視成為可以更新。INSTEAD OF 觸發程序的執行是用來取代定義觸發程序的資料修改陳述式。這個觸發程序可讓使用者指定一組為了處理資料修改陳述式而必須發生的動作。因此，如果 INSTEAD OF 觸發程序是針對某份特定資料修改陳述式 (INSERT、UPDATE 或 DELETE) 的檢視而存在，您也可以利用這個陳述式來更新對應的檢視。如需有關 INSTEAD OF 觸發程序的詳細資訊，請參閱＜設計 INSTEAD OF 觸發程序＞。

• 資料分割檢視

  如果檢視是一份資料分割檢視，在特定限制之下，這份檢視可以更新。當需要它時，Database Engine 會將本機資料分割檢視當作所有參與的資料表及檢視本身是在相同 SQL Server 執行個體的檢視，將分散式資料分割檢視當作檢視中至少有一份資料表是在不同伺服器或遠端伺服器的檢視。

  如需有關資料分割檢視的詳細資訊，請參閱＜建立資料分割檢視＞。

資料分割檢視是成員資料表的 UNION ALL 所定義的檢視，這些成員資料表的結構相同，但在相同 SQL Server 執行個體中，或在一群獨立存在的 SQL Server 執行個體 (稱為聯盟資料庫伺服器) 中，它們分別儲存成多份資料表。

在設計資料分割結構描述時，您必須清楚哪些資料屬於哪個資料分割。例如，Customers 資料表的資料散發在三個伺服器位置的三份成員資料表中：Server1 的 Customers_33、Server2 的 Customers_66，以及 Server3 的 Customers_99。

Server1 的資料分割檢視定義如下：

--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 mmeber table on Server3.
SELECT *
FROM Server3.CompanyData.dbo.Customers_99

一般而言，如果檢視的形式如下，它便是一份資料分割檢視：

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

1. 選取 list

   • 在檢視定義的資料行清單中，應該已選取成員資料表中的所有資料行。

   • 每份 select list 的相同序數位置中之各個資料行都應該是相同類型，定序也包括在內。資料行只是如同 UNION 的情況一樣，能夠隱含地轉換類型，是不足的。

     另外，至少必須有一個資料行 (如 <col>) 出現在所有選取清單的相同序數位置中。這個 <col> 的定義方式應該是 T1, ..., Tn 等成員資料表在 <col> 上分別定義了 C1, ..., Cn 等 CHECK 條件約束。

     T1 資料表所定義之 C1 條件約束的形式必須如下：

     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 等條件約束中的最多一項條件約束，因此，這些條件約束應該形成一組無關聯或不重疊的間隔。定義無關聯的條件約束之 <col> 資料行稱為資料分割資料行。請注意，在基礎資料表中，資料分割資料行可能會有不同的名稱。條件約束應該在已啟用和受信任的狀態中，才能符合先前所提到的資料分割資料行的條件。如果條件約束是停用的，請利用 ALTER TABLE 的 CHECK CONSTRAINT constraint_name 選項來重新啟用條件約束檢查，以及利用 WITH CHECK 選項來驗證它們。

     下列範例會顯示各組有效的條件約束：

     { [col < 10], [col between 11 and 20] , [col > 20] }
     { [col between 11 and 20], [col between 21 and 30], [col between 31 and 100] }
     

   • 在選取清單中，不能重複使用相同的資料行。

2. 資料分割資料行

   • 資料分割資料行是資料表 PRIMARY KEY 的一部分。

   • 它不能是計算、識別、預設或 timestamp 資料行。

   • 如果成員資料表的相同資料行有多個條件約束，Database Engine 會忽略所有條件約束，當判斷檢視是否為資料分割檢視時，並不會考量它們。若要符合資料分割檢視的條件，資料分割資料行只應該有一項資料分割條件約束。

   • 對於資料分割資料行能不能更新並沒有任何限制。

3. 成員資料表，或基礎資料表 T1, ..., Tn

   • 這些資料表可以是本機資料表或正在執行以四部分名稱或基於 OPENDATASOURCE 或 OPENROWSET 的名稱來參考的 SQL Server 之其他電腦的資料表。OPENDATASOURCE 和 OPENROWSET 語法可以指定資料表名稱，但不能是傳遞查詢。如需詳細資訊，請參閱＜OPENDATASOURCE (Transact-SQL)＞和＜OPENROWSET (Transact-SQL)＞。

     如果一或多份成員資料表在遠端，這份檢視便稱為**「分散式資料分割檢視」**，且適用其他條件。本章節稍後會加以說明。

   • 在 UNION ALL 陳述式所組合的一組資料表中，相同資料表不能出現兩次。

   • 成員資料表不能有資料表的計算資料行所建立的索引。

   • 成員資料表應該在相同數目的資料行上，具備所有 PRIMARY KEY 條件約束。

   • 檢視中的所有成員資料表都應該有相同的 ANSI 填補設定。您可以利用 sp_configure 或 SET 陳述式中的 user options 選項來設定這個項目。

下列限制適用於在資料分割檢視中修改資料的陳述式：

• INSERT 陳述式必須提供檢視中所有資料行的值，即使基礎成員資料表有這些資料行的 DEFAULT 條件約束或允許 Null 值，也是如此。對於有 DEFAULT 定義的成員資料表資料行而言，這些陳述式不能明確使用 DEFAULT 關鍵字。

• 插入資料分割資料行的值應該滿足至少一個基礎條件約束；否則，插入動作會因條件約束違規而失敗。

• UPDATE 陳述式不能指定 DEFAULT 關鍵字做為 SET 子句中的值，即使資料行有對應成員資料表所定義的 DEFAULT 值也是如此。

• 如果檢視表中的多個資料行是一個或多個成員資料表中的識別欄位，則無法利用 INSERT 或 UPDATE 陳述式來修改。

• 如果其中一個成員資料表包含 timestamp 資料行，便無法利用 INSERT 或 UPDATE 陳述式來修改資料。

• 如果成員資料表之一包含觸發程序或 ON UPDATE CASCADE/SET NULL/SET DEFAULT 或 ON DELETE CASCADE/SET NULL/SET DEFAULT 條件約束，便無法修改檢視。

• 如果陳述式中有與相同檢視或任何成員資料表的自我聯結，便不允許有針對資料分割檢視的 INSERT、UPDATE 和 DELETE 動作。

• 下列項目不支援將資料大量匯入資料分割檢視中：bcp、BULK INSERT 和 INSERT ...SELECT * FROM OPENROWSET(BULK...) 陳述式。不過，您可以使用 INSERT 陳述式，將多個資料列插入資料分割檢視中。如需詳細資訊，請參閱＜從檢視大量匯出資料或將資料大量匯入檢視＞。

  [!附註]

  若要更新資料分割檢視，使用者必須有成員資料表的 INSERT、UPDATE 和 DELETE 權限。

分散式資料分割檢視 (當一或多份成員資料表是在遠端) 適用下列其他條件：

• 將啟動分散式交易來確保跨越更新所影響的所有節點之不可部分完成的特性。

• XACT_ABORT SET 選項應該設為 ON，INSERT、UPDATE 或 DELETE 陳述式才能夠運作。

• 資料分割檢視所參考的遠端資料表中之任何 smallmoney 和 smalldatetime 資料行，分別對應為 money 和 datetime。因此，本機資料表中對應的資料行 (在選取清單的相同序數位置中) 應該是 money 和 datetime。

• 資料分割檢視中的任何連結伺服器都不能是回送連結伺服器。這是指向相同 SQL Server 執行個體的連結伺服器。

包含可更新的資料分割檢視和遠端資料表的 INSERT、UPDATE 和 DELETE 動作，其 SET ROWCOUNT 選項的設定都會被忽略。

當成員資料表和資料分割檢視定義都備妥時，SQL Server 查詢最佳化工具會建立智慧型計畫，有效地利用查詢來存取成員資料表中的資料。當使用 CHECK 條件約束定義時，查詢處理器會跨越各份成員資料表來對應索引鍵值的散發。當使用者發出查詢時，查詢處理器會比較這項對應與 WHERE 子句所指定的值，且會建立在成員伺服器之間傳送最少量資料的執行計畫。因此，雖然部分成員資料表在遠端伺服器中，但 SQL Server 執行個體仍能解析分散式查詢，只需傳送最少量的散發資料。如需有關 SQL Server 如何解析資料分割檢視之查詢的詳細資訊，請參閱＜解析分散式資料分割檢視＞。

若要在複寫所涉及的成員資料表上建立資料分割檢視，適用下列考量：

• 如果基礎資料表涉入含有更新訂閱的合併式複寫或交易式複寫，選取清單也應該包括 uniqueidentifier 資料行。

  資料分割檢視中的任何 INSERT 動作都必須提供 uniqueidentifier 資料行中的任何 NEWID() 值。uniqueidentifier 資料行的 UPDATE 動作必須提供 NEWID() 值，因為無法使用 DEFAULT 關鍵字。

• 利用檢視來進行的更新複寫與在兩個不同的資料庫中複寫資料表相同；這些資料表由不同複寫代理程式來提供，無法保證更新的順序。

下列範例會利用簡單的 SELECT 陳述式來建立檢視。當資料行組合的查詢頻率很高時，簡單檢視非常有用。這份檢視的資料來自 AdventureWorks2008R2 資料庫的 HumanResources.Employee 和 Person.Person 資料表。這項資料提供 Adventure Works Cycles 的員工名稱和雇用日期資訊。您可以為主管追蹤工作週年記錄的人建立這份檢視，但不提供他存取這些資料表所有資料的權利。

USE AdventureWorks2008R2 ;
GO
IF OBJECT_ID ('hiredate_view', 'V') IS NOT NULL
DROP VIEW hiredate_view ;
GO
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

下列範例會使用 WITH ENCRYPTION 選項，且會顯示計算資料行、重新命名的資料行和多個資料行。

USE AdventureWorks2008R2 ;
GO
IF OBJECT_ID ('Purchasing.PurchaseOrderReject', 'V') IS NOT NULL
    DROP VIEW Purchasing.PurchaseOrderReject ;
GO
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

下列範例會顯示一份名稱為 SeattleOnly 的檢視，這份檢視參考五份資料表，可讓資料修改只套用在居住在 Seattle 的員工。

USE AdventureWorks2008R2 ;
GO
IF OBJECT_ID ('dbo.SeattleOnly', 'V') IS NOT NULL
    DROP VIEW dbo.SeattleOnly ;
GO
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

下列範例會顯示包括內建函數的檢視定義。當您使用函數時，您必須指定衍生資料行的資料行名稱。

USE AdventureWorks2008R2 ;
GO
IF OBJECT_ID ('Sales.SalesPersonPerform', 'V') IS NOT NULL
    DROP VIEW Sales.SalesPersonPerform ;
GO
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

下列範例會使用名稱為 SUPPLY1、SUPPLY2、SUPPLY3 和 SUPPLY4 的資料表。這些資料表對應於四個辦公室的供應者資料表，這些辦公室在不同國家 (地區)。

--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
INSERT dbo.SUPPLY1 VALUES ('1', 'CaliforniaCorp'), ('5', 'BraziliaLtd');
INSERT dbo.SUPPLY2 VALUES ('231', 'FarEast'), ('280', 'NZ');
INSERT dbo.SUPPLY3 VALUES ('321', 'EuroGroup'), ('442', 'UKArchip');
INSERT dbo.SUPPLY4 VALUES ('475', 'India'), ('521', 'Afrique');
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;