インデックス付きビューの作成

  • [アーティクル]

更新 : 2006 年 12 月 12 日

ビューにクラスタ化インデックスを作成するには、ビューが次の必要条件を満たしている必要があります。

  • CREATE VIEW ステートメントが実行されたとき、ANSI_NULLS オプションと QUOTED_IDENTIFIER オプションが ON に設定されている必要があります。ビューのこの設定については、OBJECTPROPERTY 関数の ExecIsAnsiNullsOn プロパティまたは ExecIsQuotedIdentOn プロパティを使用して確認できます。

  • ビューで参照するテーブルを作成するすべての CREATE TABLE ステートメントの実行に対して、ANSI_NULLS オプションが ON に設定されている必要があります。

  • ビューが参照しているのはベース テーブルのみで、他のビューを参照していない必要があります。

  • ビューで参照されるすべてのベース テーブルは、ビューと同じデータベース内にあり、ビューと同じ所有者である必要があります。

  • ビューは、SCHEMABINDING オプションを使用して作成されている必要があります。スキーマ バインドによって、基になるベース テーブルのスキーマにビューがバインドされます。

  • ビューで参照されるユーザー定義関数は、SCHEMABINDING オプションを使用して作成済みである必要があります。

  • ビューでは、テーブルとユーザー定義関数を 2 つの要素で構成される名前で参照する必要があります。1 つ、3 つ、または 4 つの要素から構成される名前は使用できません。

  • ビュー内の式で参照されるすべての関数は、決定的である必要があります。OBJECTPROPERTY 関数の IsDeterministic プロパティを使用して、ユーザー定義関数が決定的かどうかを確認できます。詳細については、「決定的関数と非決定的関数」を参照してください。

    ms191432.note(ja-jp,SQL.90).gifメモ :
    SQL Server 2005 のインデックス付きビューで datetime 文字リテラルと smalldatetime 文字列リテラルを参照するときは、決定的な日付形式スタイルを使用して、そのリテラルを目的の日付型に明示的に変換することをお勧めします。決定的な日付形式スタイルの一覧については、「CAST および CONVERT (Transact-SQL)」を参照してください。datetime 型または smalldatetime 型への文字列の暗黙的な変換が必要な式は、互換性レベルが 80 以下に設定されている場合を除いて、非決定的であると見なされます。これは、サーバー セッションの LANGUAGE および DATEFORMAT の設定によって結果が異なるためです。たとえば、式 CONVERT (datetime, '30 listopad 1996', 113) では、言語が異なると文字列 'listopad' が異なる月を意味するので、結果が LANGUAGE の設定によって異なります。同様に、式 DATEADD(mm,3,'2000-12-01') の場合、SQL Server では DATEFORMAT の設定に基づいて、文字列 '2000-12-01' が解釈されます。

    照合順序間で行われる Unicode 以外の文字データの暗黙的な変換も、互換性レベルが 80 以下の場合を除いて、非決定的であると見なされます。
    互換性モードが 90 の場合は、このような式が含まれるビューのインデックスは作成できません。ただし、アップグレードされたデータベースから、このような式を含む既存のビューをメンテナンスできます。文字列から日付への暗黙的な変換を行うインデックス付きビューを使用する場合は、インデックス付きビューが破損しないように、データベースやアプリケーション内で LANGUAGE と DATEFORMAT の設定の一貫性を確保してください。

  • ビュー定義で集計関数が使用される場合、SELECT リストに COUNT_BIG (*) も指定する必要があります。

  • ユーザー定義関数のデータ アクセス プロパティが NO SQL に、外部アクセス プロパティが NO に設定されている必要があります。

  • 共通言語ランタイム (CLR) 関数をビューの選択リストに使用することはできますが、クラスタ化インデックス キーの定義に含めることはできません。CLR 関数は、ビューの WHERE 句や、ビューの JOIN 操作の ON 句では使用できません。

  • ビュー定義で使用する CLR ユーザー定義型の CLR 関数やメソッドは、次の表のようにプロパティが設定されている必要があります。

    プロパティ 注意

    DETERMINISTIC = TRUE

    Microsoft .NET Framework メソッドの属性として、明示的に宣言する必要があります。

    PRECISE = TRUE

    .NET Framework メソッドの属性として、明示的に宣言する必要があります。

    DATA ACCESS = NO SQL

    DataAccess 属性を DataAccessKind.None に、SystemDataAccess 属性を SystemDataAccessKind.None に設定することで決定されます。

    EXTERNAL ACCESS = NO

    CLR ルーチンの場合は、このプロパティの既定値は NO です。

    CLR ルーチン メソッドの属性の設定方法の詳細については、「CLR ルーチンのカスタム属性」を参照してください。

    ms191432.Caution(ja-jp,SQL.90).gif注意 :
    CLR ルーチン メソッドの機能は便利ですが、CLR ルーチン メソッドのプロパティを設定することはお勧めしません。このような設定により、データが破損する可能性があります。

  • ビューの SELECT ステートメントには、Transact-SQL 構文の次の要素は使用できません。

    • 列を指定する * または table_name**.*** 構文。列名は明示的に指定する必要があります。

    • 単純な式として使用されるテーブル列名は、複数のビュー列で指定できません。列に対するすべて、または 1 つを除くすべての参照が、複合式または関数のパラメータの一部の場合は、その列を複数回参照できます。たとえば、次の選択リストは無効です。

      SELECT ColumnA, ColumnB, ColumnA

      次の選択リストは有効です。

      SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB

    • GROUP BY 句内で使用される列での式、または集計の結果での式。

    • 派生テーブル。

    • CTE (共通テーブル式)。

    • 行セット関数。

    • UNION、EXCEPT、または INTERSECT 演算子。

    • サブクエリ。

    • 外部結合または自己結合。

    • TOP 句。

    • ORDER BY 句。

    • DISTINCT キーワード。

    • COUNT(*) (COUNT_BIG(*) は使用可能)。

    • AVG、MAX、MIN、STDEV、STDEVP、VAR、または VARP 集計関数。インデックス付きビューを参照するクエリ内で AVG(expression) が指定されている場合に、ビューの選択リストに SUM(expression) および COUNT_BIG(expression) が含まれていれば、オプティマイザは多くの場合、必要な結果を計算できます。たとえば、インデックス付きビューの SELECT リストに、AVG(column1) 式を含めることはできません。ビューの SELECT リストに SUM(column1) と COUNT_BIG(column1) という式が含まれていると、SQL Server は、ビューを参照して AVG(column1) を指定するクエリの平均値を計算できます。

    • NULL 値を許容する式を参照する SUM 関数。

    • 順位付け関数または集計関数が含まれている OVER 句。

    • CLR ユーザー定義集計関数。

    • フルテキスト述語の CONTAINS または FREETEXT。

    • COMPUTE 句または COMPUTE BY 句。

    • CROSS APPLY 演算子または OUTER APPLY 演算子。

    • PIVOT 演算子または UNPIVOT 演算子。

    • テーブル ヒント (互換性レベルが 90 以上の場合のみ)。

    • 結合ヒント。

    • Xquery 式への直接参照。スキーマにバインドされたユーザー定義関数内の Xquery 式など、間接参照の使用は可能です。

  • GROUP BY が指定されている場合、ビューの選択リストには COUNT_BIG(*) 式が含まれている必要があります。また、ビュー定義で HAVING、CUBE、または ROLLUP を指定できません。

CREATE INDEX ステートメントの要件

ビューに作成する最初のインデックスは、一意なクラスタ化インデックスにする必要があります。一意なクラスタ化インデックスを作成した後は、追加で非クラスタ化インデックスを作成できます。ビューのインデックスの名前付け規則は、テーブルのインデックスの名前付け規則と同じです。異なる点は、テーブル名がビュー名になることだけです。詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。

CREATE INDEX ステートメントは、CREATE INDEX の標準の要件以外に、次の要件を満たす必要があります。

  • CREATE INDEX ステートメントを実行するユーザーは、ビューの所有者である必要があります。
  • CREATE INDEX ステートメントが実行される場合、次の SET オプションを ON に設定する必要があります。
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
  • NUMERIC_ROUNDABORT オプションは OFF に設定する必要があります。これは既定の設定です。
  • データベースが互換性モード 80 以下で実行されている場合、ARITHABORT オプションは ON に設定する必要があります。
  • クラスタ化インデックスまたは非クラスタ化インデックスを作成する場合は、IGNORE_DUP_KEY オプションを OFF に設定する必要があります (既定の設定)。
  • text 列、ntext 列、または image 列は、CREATE INDEX ステートメントで参照されていなくても、ビューに含めることはできません。
  • ビュー定義の SELECT ステートメントで GROUP BY 句を指定した場合、一意クラスタ化インデックスのキーは、GROUP BY 句で指定した列のみ参照できます。
  • インデックス キー列の値の一部に不正確な式を使用する場合、その式は、ビューの基になるベース テーブルに格納されている列を参照していなければなりません。この列には、標準の格納済み列または保存される計算列を使用できます。その他の不正確な式は、インデックス付きビューのキー列に含めることはできません。

注意事項

インデックス付きビューの列の large_value_types_out_of_row オプションの設定は、ベース テーブルの対応する列の設定が継承されます。この値は、sp_tableoption を使用して設定します。式から形成される列に対する既定の設定は 0 です。つまり、大きい値の型は行内に格納されます。詳細については、「大きな値のデータ型の使用」を参照してください。

クラスタ化インデックスの作成後、ビューの基本データを変更しようとする接続は、インデックスの作成に必要なオプション設定と同じ設定でなければなりません。ビューの結果セットに影響を与える INSERT、UPDATE、または DELETE ステートメントを実行する接続でオプション設定が適切でない場合は、SQL Server によりエラーが生成され、各ステートメントはロールバックされます。詳細については、「結果に影響を与える SET オプション」を参照してください。

ビューが削除されると、ビューのすべてのインデックスも削除されます。クラスタ化インデックスが削除されると、ビューのすべての非クラスタ化インデックスと自動的に作成された統計も削除されます。ユーザーが作成したビューの統計は、保持されます。非クラスタ化インデックスは、個別に削除できます。ビュー上のクラスタ化インデックスを削除すると、格納された結果セットも削除され、オプティマイザは、ビューの処理を標準的なビューと同様の処理に戻します。

CREATE UNIQUE CLUSTERED INDEX ステートメントでは、クラスタ化インデックス キーを構成する列のみが指定されますが、ビューの完全な結果セットはデータベースに格納されます。ベース テーブルのクラスタ化インデックスと同様に、クラスタ化インデックスの B ツリー構造にはキー列だけが格納されますが、データ行にはビューの結果セットのすべての列が格納されます。

インデックスを既存のシステムのビューに追加する場合、インデックスを作成するビューにスキーマをバインドする必要があります。次の操作を実行できます。

  • ビューを削除し、WITH SCHEMABINDING を指定してビューを再作成できます。
  • 既存のビューと同じテキストで、名前が異なる 2 番目のビューを作成できます。オプティマイザは、クエリの FROM 句で新しいビューのインデックスが直接参照されていなくても、そのインデックスを考慮します。
    ms191432.note(ja-jp,SQL.90).gifメモ :
    SCHEMABINDING 句を指定して作成したビューに参加しているビューまたはテーブルは、そのビューが削除または変更されてスキーマ バインドがなくならない限り削除できません。さらに、スキーマ バインドを含むビューに参加しているテーブルに対する ALTER TABLE ステートメントは、そのステートメントがビュー定義に影響を与える場合は、失敗します。

新しいビューが、インデックス付きビューの要件をすべて満たしていることを確認する必要があります。このため、ビュー、および参照されるすべてのベース テーブルの所有権を変更して、同じユーザーの所有にする必要が生じる場合があります。

テーブルとビューのインデックスは無効にされる可能性があります。テーブルのクラスタ化インデックスが無効になると、そのテーブルに関連するビューのインデックスも無効になります。詳細については、「インデックスの無効化」を参照してください。

例

次の例では、ビューとそのビューのインデックスを作成します。ここでは、インデックス付きビューを使用する 2 つのクエリを実行します。

USE AdventureWorks;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

参照

概念

インデックス付きビューのデザイン
インデックス付きビューの DBCC エラーに関するトラブルシューティング

その他の技術情報

CREATE INDEX (Transact-SQL)
SET ANSI_NULLS (Transact-SQL)
SET ANSI_PADDING (Transact-SQL)
SET ANSI_WARNINGS (Transact-SQL)
SET ARITHABORT (Transact-SQL)
SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
SET NUMERIC_ROUNDABORT (Transact-SQL)
SET QUOTED_IDENTIFIER (Transact-SQL)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース 履歴

2006 年 12 月 12 日
新しい内容 :
  • Xquery 式への直接参照をインデックス付きビュー定義に使用できないことを、ビューの要件の一覧に追加しました。

2006 年 7 月 17 日
新しい内容 :
  • SELECT ステートメントに PIVOT 演算子または UNPIVOT 演算子が含まれている場合、ビューにインデックスを付けられないことを追加しました。

2006 年 4 月 14 日
新しい内容 :
  • 「例」を追加しました。
  • データベースが互換性モード 80 以下で実行されている場合、インデックス付きビューを作成するには ARITHABORT オプションを ON に設定する必要があることを説明した注記を追加しました。
  • ビューの定義に順位付け関数または集計関数が含まれている場合、ビューにはインデックスを作成できないことを追加しました。