CREATE VIEW (Transact-SQL)

次の条件を満たす場合、ビューから基になるデータベース テーブルのデータを変更できます。

• UPDATE、INSERT、DELETE ステートメントなどの変更で、1 つのベース テーブルのみの列を参照している。

• ビューにある変更対象の列が、テーブルの列内にある基になるデータを直接参照している。ただし次のような方法では、列は派生されません。

  • 集計関数 (AVG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、VAR、および VARP)。

  • 計算。他の列を使用する式を基にして列を計算することはできません。計算に相当する set 演算子 UNION、UNION ALL、CROSSJOIN、EXCEPT、INTERSECT を使用して形成される列も計算されません。

• 変更される列が、GROUP BY、HAVING、または DISTINCT 句の影響を受けない。

• ビューの select_statement 内で、TOP が WITH CHECK OPTION 句と共に使用されていない。

以前の制限は、ビュー自体に適用されるのと同様に、ビューの FROM 句のサブクエリにも適用されます。通常は、データベース エンジンでは、ビュー定義からベース テーブルへの変更を正確に追跡できる必要があります。詳細については、「ビューを使用したデータ変更」を参照してください。

以前の制約によってビューから直接データを変更できない場合は、次の方法を試してください。

• INSTEAD OF トリガー

  ビューに INSTEAD OF トリガーを作成して、そのビューを更新可能にできます。INSTEAD OF トリガーは、そのトリガーが定義されているデータ変更ステートメントの代わりに実行されます。このトリガーで、データ変更ステートメントを処理する一連の操作を指定できます。したがって、特定のデータ変更ステートメント (INSERT、UPDATE、または DELETE) に対する INSTEAD OF トリガーがビューに存在する場合は、そのステートメントを介して対応するビューを更新できます。INSTEAD OF トリガーの詳細については、「INSTEAD OF トリガのデザイン」を参照してください。

• パーティション ビュー

  ビューがパーティション ビューである場合は、いくつかの制限の範囲内で更新可能です。必要に応じて、データベース エンジンでは、ローカル パーティション ビュー (参加しているすべてのテーブルとビューが同じ SQL Server インスタンス上にあるビュー) と分散パーティション ビュー (ビュー内の少なくとも 1 つのテーブルが別のサーバーまたはリモート サーバー上にあるビュー) が区別されます。

  パーティション ビューの詳細については、「パーティション ビューの作成」を参照してください。

パーティション ビューは、同じ構造のメンバー テーブルの UNION ALL によって定義されるビューです。ただし、これらのメンバー テーブルは、同じ SQL Server インスタンス内、または連合データベース サーバーと呼ばれる SQL Server サーバー インスタンスの独立したグループ内に、複数のテーブルとして個別に格納されます。

注
1 台のサーバーに対してローカルでデータをパーティション分割する方法としては、パーティション テーブルをお勧めします。詳細については、「パーティション テーブルとパーティション インデックス」を参照してください。

パーティション分割構成の設計時には、各パーティションに所属するデータを明確にする必要があります。たとえば、Customers テーブルのデータは、3 か所のサーバー上の 3 つのメンバー テーブル (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. 選択リスト (select list)

   • メンバー テーブルのすべての列は、ビュー定義の列リストで選択する必要があります。

   • それぞれの select list の同じ位置にある列は、照合順序も含めて同じ型であることが必要です。列が暗黙的に変換される型であるという条件だけでは十分ではありません。これは UNION の場合とは異なります。

     また、すべての選択リストの同じ位置に、少なくとも 1 つの列 (たとえば <col>) が指定されている必要があります。この <col> は、メンバー テーブル T1, ..., Tn の <col> にそれぞれ CHECK 制約 C1, ..., Cn を指定することで定義します。

     テーブル 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 の制約の 1 つにのみ該当するような形式にする必要があります。つまり、連続せずかつ重複しない間隔を持つ制約セットを形成するように定義します。連続しない制約が定義されている列 <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 の一部です。

   • 計算列、ID 列、既定の列、または timestamp 列に対して指定することはできません。

   • メンバー テーブルの 1 つの列に複数の制約が定義されている場合、データベース エンジンではすべての制約が無視され、ビューがパーティション ビューであるかどうかを判断する際にそれらの制約は考慮されません。パーティション ビューの条件を満たすには、パーティション分割列にパーティション分割制約を 1 つだけ定義する必要があります。

   • パーティション分割列の更新可能性に制限はありません。

3. メンバー テーブルまたは基になるテーブル T1, ..., Tn

   • テーブルは、ローカル テーブルまたは SQL Server が実行されている他のコンピューター上のテーブルのいずれかになります。他のコンピューター上のテーブルの場合は、4 つの要素で構成される名前か、OPENDATASOURCE ベースまたは OPENROWSET ベースの名前で参照されます。OPENDATASOURCE および OPENROWSET の構文では、テーブル名は指定できますが、パススルー クエリは指定できません。詳細については、「OPENDATASOURCE (Transact-SQL)」および「OPENROWSET (Transact-SQL)」を参照してください。

     1 つ以上のメンバー テーブルがリモートにある場合、そのビューは分散パーティション ビューと呼ばれ、さらに条件が適用されます。これらの条件については後で説明します。

   • UNION ALL ステートメントで組み合わせるテーブルのセットに、同じテーブルを 2 回指定することはできません。

   • メンバー テーブルでは、テーブル内の計算列上にインデックスを作成することはできません。

   • メンバー テーブルでは、すべての PRIMARY KEY 制約が同じ数の列に対して定義されている必要があります。

   • ビューのすべてのメンバー テーブルには、同じ ANSI PADDING 設定を指定する必要があります。これは、sp_configure の user options オプションまたは SET ステートメントを使用して設定できます。

パーティション ビューのデータを変更するステートメントには、次の制限が適用されます。

• INSERT ステートメントでは、基になるメンバー テーブルで列に DEFAULT 制約が定義されているか NULL 値が許容されている場合でも、ビューのすべての列に値を提供する必要があります。メンバー テーブルの列に DEFAULT 定義がある場合、ステートメントではこれらの列に DEFAULT キーワードを使用できません。

• パーティション分割列に挿入する値は、基になる制約の少なくとも 1 つを満たしている必要があります。満たしていない場合、INSERT 操作は制約違反で失敗します。

• UPDATE ステートメントでは、対応するメンバー テーブルで列の DEFAULT 値が定義されている場合でも、SET 句の値として DEFAULT キーワードを指定することはできません。

• ビューの列が、1 つ以上のメンバー テーブルで ID 列になっている場合、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 の各権限を持っている必要があります。

分散パーティション ビュー (1 つ以上のメンバー テーブルがリモートの場合) では、次の追加条件が適用されます。

• 更新によって影響を受けるすべてのノードにおいて原子性を保証するため、分散トランザクションが起動されます。

• INSERT、UPDATE、または DELETE ステートメントが動作するには、XACT_ABORT SET オプションを ON に設定する必要があります。

• パーティション ビューで参照されるリモート テーブルに 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 操作では、DEFAULT キーワードを使用できないので、NEWID() を値として指定する必要があります。

• ビューを使用した更新のレプリケーションは、2 つの異なるデータベースでのテーブルのレプリケーションと同じです。つまり、テーブルは異なるレプリケーション エージェントで管理されるため、更新の順序は保証されません。

次の例では、単純な 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

次の例では、5 つのテーブルを参照する SeattleOnly というビューを表示し、シアトル在住の従業員だけにデータ変更を許可します。

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 というテーブルを使用します。これらのテーブルは、異なる国や地域にある 4 か所のオフィスの仕入れ先テーブルに対応しています。

--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;