インデックス付きビューの作成
適用対象: SQL Server
Azure SQL Database
Azure 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
オプションは、以下の条件が発生するたびに、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 |
1 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 列 スパース列セット 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」を参照してください。