インデックス付きビューの作成
適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
この記事では、ビューにインデックスを作成する方法について説明します。 ビューに作成する最初のインデックスは、一意なクラスター化インデックスにする必要があります。 一意のクラスター化インデックスを作成した後は、非クラスター化インデックスを追加で作成できます。 ビューに一意のクラスター化インデックスを作成すると、そのビューは、クラスター化インデックスが定義されているテーブルと同じ方法でデータベースに格納されるので、クエリのパフォーマンスが向上します。 クエリ オプティマイザーではインデックス付きビューを使って、クエリの実行速度を高めることができます。 オプティマイザーでビューを代用するかどうかを判別するために、ビューがクエリで参照されている必要はありません。
手順
次の手順は、インデックス付きビューの作成に必要な手順であり、インデックス付きビューの正常な実装に不可欠です。
SETオプションが、ビューで参照されるすべての既存のテーブルに対して正しいことを確認します。- テーブルやビューを作成する前に、そのセッション用の SET オプションが正しく設定されていることを確認します。
- ビュー定義が決定的であることを確認します。
- ベース テーブルの所有者がビューと同じであることを確認します。
WITH SCHEMABINDINGオプションを使用して、ビューを作成します。- ビューに一意のクラスター化インデックスを作成します。
重要
多数のインデックス付きビュー、または少数ではあるものの非常に複雑なインデックス付きビューで参照されるテーブルに対して 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 オプションと、その値 ( 必要な値 の列を参照) を下の表に示します。この設定は次の条件に該当する場合に常に必要となります。
- ビューが作成され、そのビューのインデックスも作成されている。
- ビューの作成時にビューで参照されるベース テーブル。
- インデックス付きビューに関与するテーブルで実行される挿入、更新、または削除操作がある。 この要件には一括コピー、レプリケーション、分散クエリなどの操作も含まれます。
- クエリ オプティマイザーで、クエリ プランの生成にインデックス付きビューが使用される。
| 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 |
1ANSI_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 つの部分から構成される名前で参照される必要があります。
ユーザー定義関数のデータ アクセス プロパティが
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オプションを使用して作成する必要があります。ビューが、ビューと同じデータベース内のベース テーブルのみを参照していること。 ビューでは、他のビューを参照できません。
GROUP BYが存在する場合、VIEW 定義にはCOUNT_BIG(*)を含める必要があります。HAVINGを含めることはできません。 このようなGROUP BY制限は、インデックス付きビュー定義にのみ適用されます。 クエリがこのGROUP BY制限を満たしていない場合でも、実行プランでインデックス付きビューを使用することはできます。ビュー定義に
GROUP BY句が含まれている場合、一意のクラスター化インデックスのキーでは、GROUP BY句で指定した列のみを参照できます。ビュー定義の SELECT ステートメントには、次の Transact-SQL 構文を使用できません。
Transact-SQL 関数 考えられる代替候補 COUNTCOUNT_BIGを使用します行セット関数 ( 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) WITHSELECTサブクエリ SELECTSELECT [ <table>. ] *列に明示的に名前を付ける SELECTSELECT DISTINCTGROUP BYを使用しますSELECTSELECT TOPSELECT順位付け関数または集計関数が含まれている OVER句FROMLEFT OUTER JOINFROMRIGHT OUTER JOINFROMFULL OUTER JOINFROMOUTER APPLYFROMCROSS APPLYFROM派生テーブル式 (つまり、 SELECT句でFROMを使用)FROM自己結合 FROMテーブル変数 FROMインライン テーブル値関数 FROM複数ステートメント テーブル値関数 FROMPIVOT、UNPIVOTFROMTABLESAMPLEFROMFOR SYSTEM_TIMEテンポラル履歴テーブルに直接クエリを実行する WHEREフルテキスト述語 ( CONTAINS、FREETEXT、CONTAINSTABLE、FREETEXTTABLE)GROUP BYCUBE、ROLLUP、またはGROUPING SETSの演算子GROUP BY列の組み合わせごとに個別のインデックス付きビューを定義するGROUP BYHAVING集合演算子 UNION、UNION ALL、EXCEPT、INTERSECTOR句でAND、AND NOT、WHEREをそれぞれ使用するORDER BYORDER BYORDER BYOFFSETソース列タイプ 考えられるな代替候補 非推奨の大きな値の列タイプ text、 ntext、および image 列をそれぞれ varchar(max)、nvarchar(max)、varbinary (max) に移行します。 xml 列または FILESTREAM 列 インデックス キーの float1 列 スパース列セット 1 インデックス付きビューには float 列を含めることができますが、このような列はクラスター化インデックス キーには含めることができません。
重要
テンポラル クエリ (
FOR SYSTEM_TIME句を使うクエリ) 上では、インデックス付きビューはサポートされていません。
datetime および smalldatetime の推奨事項
インデックス付きビューで datetime 文字リテラルと smalldatetime 文字列リテラルを参照するときは、決定的な日付形式スタイルを使用して、そのリテラルを目的の日付型に明示的に変換することをお勧めします。 決定論的な日付形式の一覧については、「CAST および CONVERT (Transact-SQL)」を参照してください。 決定的な式と非決定的な式の詳細については、このページの「考慮事項」セクションを参照してください。
datetime 型または smalldatetime 型への文字列の暗黙的な変換が必要な式は非決定的であると見なされます。 詳細については、「リテラル日付文字列を DATE 値に非決定論的に変換する」を参照してください。
インデックス付きビューに関するパフォーマンス上の考慮事項
多数のインデックス付きビュー、または少数ではあるものの複雑なインデックス付きビューで参照されるテーブルに対して DML (UPDATE、DELETE、INSERT など) を実行する場合、DML 実行時にこれらのインデックス付きビューを更新する必要もあります。 その結果、DML クエリのパフォーマンスが大幅に低下する場合があります。また、場合によっては、クエリ プランを生成できないこともあります。 このようなシナリオでは、運用環境で使用する前に DML クエリをテストし、クエリ プランを分析してから DML ステートメントを調整/簡素化します。
データベース エンジンでインデックス付きビューが使用されないようにするには、クエリに OPTION (EXPAND VIEWS) ヒントを含めます。 これによって、オプションの 1 つが正しく設定されていない場合、オプティマイザーもビューのインデックスを使用できません。 OPTION (EXPAND VIEWS) ヒントの詳細については、「SELECT (Transact-SQL)」を参照してください。
さまざまな追加の考慮事項
インデックス付きビューの列の 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
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
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 (Transact-SQL)」を参照してください。
次のステップ
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示