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

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

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

手順

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

1. SET オプションが、ビューで参照されるすべての既存のテーブルに対して正しいことを確認します。
2. テーブルやビューを作成する前に、そのセッション用の SET オプションが正しく設定されていることを確認します。
3. ビュー定義が決定的であることを確認します。
4. ベース テーブルの所有者がビューと同じであることを確認します。
5. WITH SCHEMABINDING オプションを使用して、ビューを作成します。
6. ビューに一意のクラスター化インデックスを作成します。

多数のインデックス付きビュー、または少数ではあるものの複雑なインデックス付きビューで参照されるテーブルに対して UPDATE、DELETE、INSERT の操作 (データ操作言語または DML) を実行する場合、これらの参照されるインデックス付きビューを更新する必要もあります。 その結果、DML クエリのパフォーマンスが大幅に低下する場合があります。また、場合によっては、クエリ プランを生成できないこともあります。

このようなシナリオでは、運用環境で使用する前に DML クエリをテストし、クエリ プランを分析してから DML ステートメントを調整/簡素化します。

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

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

ビューが正しく維持され、一貫性のある結果が返されるようにするには、インデックス付きビューで、いくつかの SET オプションに固定値が必要となります。 以下のテーブルの SET オプションは、以下の条件が発生するたびに、Required value 列に示された値に設定される必要があります:

• ビューが作成され、そのビューのインデックスも作成されている。
• ビューの作成時にビューで参照されるベース テーブル。
• インデックス付きビューに関与するテーブルで挿入、更新、または削除が実行される場合。 この要件には一括コピー、レプリケーション、分散クエリなどの操作も含まれます。
• クエリ オプティマイザーで、クエリ プランの生成にインデックス付きビューが使用される。

SET オプション	必須値	既定のサーバー値	Default OLE DB および ODBC 値	Default DB-Library 値
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS 1	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 に設定すると、暗黙的に ARITHABORT が ON に設定されます。

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

重要

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

決定論的なビューの要件

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

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

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

その他の要件

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

• CREATE INDEX を実行するユーザーが、ビューの所有者である必要があります。

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

• ビュー定義では、<schema>.<tablename> という 2 つの部分から構成される名前でテーブルが参照される必要があります。

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

• ビューで参照されるユーザー定義関数は、2 つの部分で構成されている名前 <schema>.<function> で参照される必要があります。

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

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

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

  プロパティ	Note
  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 オプションを使用して作成する必要があります。

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

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

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

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

  Transact-SQL 関数	考えられる代替候補
  COUNT	COUNT_BIG を使用します
  ROWSET 関数 (OPENDATASOURCE、OPENQUERY、OPENROWSET、および OPENXML)
  算術平均 (AVG)	個別の列として COUNT_BIG と SUM を使用する
  統計集計関数 (STDEV、STDEVP、VAR、および VARP)
  NULL 値を許容する式を参照する SUM 関数	SUM() 内の ISNULL を使用して式を null 非許容にする
  その他の集計関数 (MIN、MAX、CHECKSUM_AGG、および STRING_AGG)
  ユーザー定義集計関数 (SQL CLR)

  SELECT 句	Transact-SQL の要素	考えられるな代替候補
  WITH cte AS	共通テーブル式 (CTE) WITH
  SELECT	サブクエリ
  SELECT	SELECT [ <table>. ] *	列に明示的に名前を付ける
  SELECT	SELECT DISTINCT	GROUP BY を使用します
  SELECT	SELECT TOP
  SELECT	順位付け関数または集計関数が含まれている OVER 句
  FROM	LEFT OUTER JOIN
  FROM	RIGHT OUTER JOIN
  FROM	FULL OUTER JOIN
  FROM	OUTER APPLY
  FROM	CROSS APPLY
  FROM	派生テーブル式 (つまり、SELECT 句で FROM を使用)
  FROM	自己結合
  FROM	テーブル変数
  FROM	インライン テーブル値関数
  FROM	複数ステートメント テーブル値関数
  FROM	PIVOT、UNPIVOT
  FROM	TABLESAMPLE
  FROM	FOR SYSTEM_TIME	テンポラル履歴テーブルに直接クエリを実行する
  WHERE	フルテキスト述語 (CONTAINS、FREETEXT、CONTAINSTABLE、FREETEXTTABLE)
  GROUP BY	CUBE、ROLLUP、または GROUPING SETS の演算子	GROUP BY 列の組み合わせごとに個別のインデックス付きビューを定義する
  GROUP BY	HAVING
  集合演算子	UNION、UNION ALL、EXCEPT、INTERSECT	OR 句で AND、AND NOT、WHERE をそれぞれ使用する
  ORDER BY	ORDER BY
  ORDER BY	OFFSET

  ソース列タイプ	考えられるな代替候補
  非推奨の大きな値の列タイプ (text、 ntext、および image)	列をそれぞれ varchar(max)、nvarchar(max)、varbinary (max) に移行します。
  xml 列または FILESTREAM 列
  インデックス キーの float 1 列
  スパース列セット

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

  重要

  テンポラル クエリ (FOR SYSTEM_TIME 句を使うクエリ) 上では、インデックス付きビューはサポートされていません。

datetime および smalldatetime の推奨事項

インデックス付きビューで datetime 文字リテラルと smalldatetime 文字列リテラルを参照するときは、決定的な日付形式スタイルを使用して、そのリテラルを目的の日付型に明示的に変換することをお勧めします。 決定的な日付形式の一覧については、「 CAST および CONVERT」を参照してください。 決定的な式と非決定的な式の詳細については、このページの「考慮事項」セクションを参照してください。

datetime 型または smalldatetime 型への文字列の暗黙的な変換が必要な式は非決定的であると見なされます。 詳細については、「リテラル日付文字列を DATE 値に非決定論的に変換する」を参照してください。

インデックス付きビューに関するパフォーマンス上の考慮事項

多数のインデックス付きビュー、または少数ではあるものの複雑なインデックス付きビューで参照されるテーブルに対して DML (UPDATE、DELETE、INSERT など) を実行する場合、DML 実行時にこれらのインデックス付きビューを更新する必要もあります。 その結果、DML クエリのパフォーマンスが大幅に低下する場合があります。また、場合によっては、クエリ プランを生成できないこともあります。 このようなシナリオでは、運用環境で使用する前に DML クエリをテストし、クエリ プランを分析してから DML ステートメントを調整/簡素化します。

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

その他の考慮事項

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

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

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

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

アクセス許可

ビューを作成するには、データベースの CREATE VIEW アクセス許可と、ビューが作成されているスキーマの ALTER アクセス許可が必要です。 ベース テーブルが別のスキーマ内に存在する場合、少なくともそのテーブルに対する REFERENCES アクセス許可が必要です。 インデックスを作成するユーザーとビューを作成したユーザーが異なる場合は、インデックスを作成するときに、ビューに対する ALTER 権限が必要です (スキーマの ALTER によって処理されます)。

インデックスは、オーナーが参照先のテーブルと同じビューにのみ作成できます。 この概念は、ビューとテーブル間の完全な 所有権の継承 とも呼ばれます。 通常、テーブルとビューが同じスキーマ内に存在する場合、そのスキーマ内のすべてのオブジェクトに同じスキーマのオーナーが適用されます。 つまり、そのビューのオーナーにならずに、ビューを作成することが可能です。 一方で、スキーマ内の個別のオブジェクトの所有者を明示的に別にすることも可能です。 所有者がスキーマのオーナーと異なる場合、sys.tables の principal_id 列に値が格納されます。

インデックス付きビューの作成: T-SQL の例

次の例では、AdventureWorks データベースにビューとそのビューのインデックスを作成します。

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

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

次の 2 つのクエリは、FROM 句でビューが指定されていない場合でも、インデックス付きビューを使用する方法を示しています。

--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
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '05/01/2012', 101)
WHERE od.ProductID BETWEEN 700
        AND 800
GROUP BY OrderDate,
    ProductID
ORDER BY Rev DESC;
GO

--This query will also use the above indexed view.
SELECT OrderDate,
    SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
        AND o.OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
GROUP BY OrderDate
ORDER BY OrderDate ASC;

最後に、この例では、インデックス付きビューから直接クエリを実行する方法を示します。 SQL Server 2016 (13.x) Service Pack 1 より前では、SQL Server の特定のエディションでのみ、クエリ オプティマイザーによるインデックス付きビューの自動的な使用がサポートされています。 SQL Server Standard エディションでは、NOEXPAND クエリ ヒントを使用してインデックス付きビューに直接クエリを実行する必要があります。 SQL Server 2016 (13.x) Service Pack 1 以降、すべてのエディションでインデックス付きビューの自動使用がサポートされています。 また、Azure SQL Database および Azure SQL Managed Instance では、NOEXPAND ヒントを指定しなくても、インデックス付きビューの自動的な使用もサポートされます。 詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

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

関連するコンテンツ

• 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)