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

  • [アーティクル]

このトピックでは、SQL Server 2012 で、Transact-SQL を使用し、インデックス付きビューを作成する方法について説明します。 ビューに作成する最初のインデックスは、一意なクラスター化インデックスにする必要があります。 一意なクラスター化インデックスを作成した後は、非クラスター化インデックスを作成できます。 ビューに一意のクラスター化インデックスを作成すると、そのビューは、クラスター化インデックスが定義されているテーブルと同じ方法でデータベースに格納されるので、クエリのパフォーマンスが向上します。 クエリ オプティマイザーではインデックス付きビューを使って、クエリの実行速度を高めることができます。 オプティマイザーでビューを代用するかどうかを判別するために、ビューがクエリで参照されている必要はありません。

このトピックの内容

  • 作業を開始する準備:

    制限事項と制約事項

    推奨事項

    考慮事項

    セキュリティ

  • 以下を使用してインデックス付きビューを作成するには:

    Transact-SQL

作業を開始する準備

次の手順は、インデックス付きビューの作成に必要な手順であり、インデックス付きビューの正常な実装に不可欠です。

  1. SET オプションが、ビューで参照されるすべての既存のテーブルに対して正しいことを確認します。

  2. 新しいテーブルやビューを作成する前に、そのセッション用の SET オプションが正しく設定されていることを確認します。

  3. ビュー定義が決定的であることを確認します。

  4. WITH SCHEMABINDING オプションを使ってビューを作成します。

  5. ビューに一意のクラスター化インデックスを作成します。

インデックス付きビューに必要な SET オプション

クエリの実行時、異なる SET オプションがアクティブになっている場合、データベース エンジン は同じ式を評価しても異なる結果を生成することがあります。 たとえば、SET オプションの CONCAT_NULL_YIELDS_NULL を ON に設定した後、式 'abc' + NULL を実行すると NULL 値が返されますが、 CONCAT_NULL_YIELDS_NULL を OFF に設定した後、同じ式を実行すると値 'abc' が返されます。

ビューが正しく維持され、一貫性のある結果が返されるようにするには、インデックス付きビューで、いくつかの SET オプションに固定値が必要となります。 固定値の設定が必要な SET オプションと、その値 (必要な値の列を参照) を下の表に示します。この設定は次の条件に該当する場合に常に必要となります。

  • ビューが作成され、そのビューのインデックスも作成されている。

  • テーブルの作成時にビューで参照されるベース テーブル。

  • インデックス付きビューに関与するテーブルで実行される挿入、更新、または削除操作がある。 この要件には一括コピー、レプリケーション、分散クエリなどの操作も含まれます。

  • クエリ オプティマイザーで、クエリ プランの生成にインデックス付きビューが使用される。

    SET オプション

    必要な値

    既定のサーバー値

    既定の

    OLE DB および ODBC 値

    既定の

    DB-Library 値

    ANSI_NULLS

    ON

    ON

    ON

    OFF

    ANSI_PADDING

    ON

    ON

    ON

    OFF

    ANSI_WARNINGS*

    ON

    ON

    ON

    OFF

    ARITHABORT

    ON

    ON

    OFF

    OFF

    CONCAT_NULL_YIELDS_NULL

    ON

    ON

    ON

    OFF

    NUMERIC_ROUNDABORT

    OFF

    OFF

    OFF

    OFF

    QUOTED_IDENTIFIER

    ON

    ON

    ON

    OFF

    *ANSI_WARNINGS を ON に設定すると、データベース互換性レベルが 90 以上に設定されている場合、暗黙的に ARITHABORT が ON に設定されます。

OLE DB または ODBC サーバー接続を使用している場合、変更する必要があるのは ARITHABORT 設定の値だけです。 すべての DB-Library 値は、サーバー レベルで sp_configure を使用するか、アプリケーションから SET コマンドを使用して、正しく設定する必要があります。

重要な注意事項重要

ARITHABORT ユーザー オプションは、そのサーバーのデータベースで初めてインデックス付きビューまたは計算列のインデックスが作成されたときすぐに、サーバー全体で ON に設定することを強くお勧めします。

決定的なビュー

インデックス付きビューの定義は決定的である必要があります。 選択リストのすべての式と、WHERE 句および GROUP BY 句が決定的である場合、ビューは決定的であるといえます。 決定的な式では、特定の入力値セットで評価するとき常に同じ結果が返されます。 決定的な式には、決定的な関数のみを含めることができます。 たとえば、DATEADD 関数は、3 つのパラメーターの任意の引数値セットに対して常に同じ結果を返すため、決定的であるといえます。 GETDATE は、常に同じ引数で起動されるにもかかわらず、返す値は実行のたびに変化するため、非決定的であるといえます。

ビュー列が決定的かどうかを判断するには、COLUMNPROPERTY 関数の IsDeterministic プロパティを使用します。 スキーマ バインドを含むビューの決定的な列が正確であるかどうかを判断するには、COLUMNPROPERTY 関数の IsPrecise プロパティを使用します。COLUMNPROPERTY では、TRUE の場合は 1、FALSE の場合は 0、有効でない入力に対しては NULL が返されます。 これは、列が決定的でないか、正確でないことを表します。

式が決定的でも、浮動小数点式が含まれる場合は、正確な結果はプロセッサのアーキテクチャまたはマイクロコードのバージョンによって異なる可能性があります。 データの整合性を確保するため、このような式は、インデックス付きビューの非キー列としてのみ含めることができます。 浮動小数点式を含まない決定的な式は、正確な式です。 インデックス ビューのキー列と WHERE または GROUP BY 句には、正確で決定的な式だけを含めることができます。

[トップに戻る] リンクで使用される矢印アイコン[先頭に戻る]

その他の要件

SET オプションと決定的な関数の要件に加えて、次の要件を満たす必要があります。

  • CREATE INDEX を実行するユーザーが、ビューの所有者であること。

  • インデックスを作成する場合は、IGNORE_DUP_KEY オプションを OFF に設定する必要があります (既定の設定)。

  • ビュー定義では、schema**.**tablename という 2 つの部分から構成される名前でテーブルが参照されていること。

  • ビューで参照されているユーザー定義関数が、WITH SCHEMABINDING オプションを使用して作成されていること。

  • ビューで参照されているユーザー定義関数が、schema**.**function という 2 つの部分から構成される名前で参照されていること。

  • ユーザー定義関数のデータ アクセス プロパティが 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 です。

  • ビューが、WITH SCHEMABINDING オプションを使って作成されていること。

  • ビューが、ビューと同じデータベース内のベース テーブルのみを参照していること。 ビューでは、他のビューを参照できません。

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

    COUNT

    行セット関数 (OPENDATASOURCE、OPENQUERY、OPENROWSET、および OPENXML)

    外部結合 (LEFT、RIGHT、または FULL)

    派生テーブル (FROM 句で SELECT ステートメントを指定することで定義される)

    自己結合

    SELECT * または SELECT table_name.* を使用して、列を指定します

    DISTINCT

    STDEV、STDEVP、VAR、VARP、または AVG

    共通テーブル式 (CTE)

    float*、text、ntext、image、XML、または filestream 列

    サブクエリ

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

    フルテキスト述語 (CONTAIN、FREETEXT)

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

    ORDER BY

    CLR ユーザー定義集計関数

    先頭に戻る

    CUBE、ROLLUP、または GROUPING SETS 演算子

    MIN、MAX

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

    TABLESAMPLE

    テーブル変数

    OUTER APPLY または CROSS APPLY

    PIVOT、UNPIVOT

    スパース列セット

    インラインまたは複数ステートメントのテーブル値関数

    OFFSET

    CHECKSUM_AGG

    *インデックス付きビューには float 列を含めることができますが、このような列はクラスター化インデックス キーには含めることができません。

  • GROUP BY が存在する場合、VIEW 定義には COUNT_BIG(*) を含める必要があります。HAVING を含めることはできません。 このような GROUP BY 制限は、インデックス付きビュー定義にのみ適用されます。 クエリがこの GROUP BY 制限を満たしていない場合でも、実行プランでインデックス付きビューを使用することはできます。

  • ビュー定義に GROUP BY 句を指定した場合、一意のクラスター化インデックスのキーでは、GROUP BY 句で指定した列のみを参照できること。

推奨事項

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

照合順序間で行われる Unicode 以外の文字データの暗黙的な変換も非決定的であると見なされます。

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

考慮事項

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

インデックス付きビューはパーティション分割されたテーブルに作成でき、インデックス付きビュー自体をパーティション分割できます。

データベース エンジンでインデックス付きビューが使用されないようにするには、クエリに OPTION (EXPAND VIEWS) ヒントを含めます。 これによって、オプションの 1 つが正しく設定されていない場合、オプティマイザーもビューのインデックスを使用できません。 OPTION (EXPAND VIEWS) ヒントの詳細については、「SELECT (Transact-SQL)」を参照してください。

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

テーブルとビューのインデックスは無効にされる可能性があります。 テーブルのクラスター化インデックスが無効になると、そのテーブルに関連するビューのインデックスも無効になります。

[トップに戻る] リンクで使用される矢印アイコン[先頭に戻る]

セキュリティ

権限

データベースの CREATE VIEW 権限と、ビューが作成されているスキーマの ALTER 権限が必要です。

[トップに戻る] リンクで使用される矢印アイコン[先頭に戻る]

Transact-SQL の使用

インデックス付きビューを作成するには

  1. オブジェクト エクスプローラーで、データベース エンジンのインスタンスに接続します。

  2. [標準] ツール バーの [新しいクエリ] をクリックします。

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、[実行] をクリックします。 この例では、ビューとそのビューのインデックスを作成します。 ここでは、インデックス付きビューを使用する 2 つのクエリを実行します。

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

詳細については、「CREATE VIEW (Transact-SQL)」を参照してください。

[トップに戻る] リンクで使用される矢印アイコン[先頭に戻る]

関連項目

参照

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)