フィルタ選択されたインデックスのデザイン ガイドライン

クエリに関連する少数の値だけが列に含まれている場合、値のサブセットにフィルタ選択されたインデックスを作成できます。たとえば、列の値がほとんど NULL の場合に、クエリで常に NULL 以外の値を選択するときは、NULL 以外のデータ行にフィルタ選択されたインデックスを作成できます。作成したインデックスは、同じキー列に定義されているテーブル全体の非クラスタ化インデックスよりも小さく、メンテナンス コストが少なくなります。

たとえば、AdventureWorks データベースには、2679 行の Production.BillOfMaterials テーブルがあります。EndDate 列では、NULL 以外の値を含む行は 199 行だけで、他の 2480 行には NULL が含まれています。次のフィルタ選択されたインデックスは、インデックスで定義された列を返し、EndDate で NULL 以外の値を含む行のみを選択するクエリに対応します。

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

フィルタ選択されたインデックス FIBillOfMaterialsWithEndDate は、次のクエリに対して有効です。クエリ実行プランを表示して、クエリ オプティマイザでフィルタ選択されたインデックスが使用されたかどうかを確認できます。クエリ実行プランの表示方法の詳細については、「クエリの分析」を参照してください。

SELECT ProductAssemblyID, ComponentID, StartDate 
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL 
    AND ComponentID = 5 
    AND StartDate > '01/01/2008' ;
GO

フィルタ選択されたインデックスの作成方法およびフィルタ選択されたインデックスの述語式の定義方法の詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。

テーブルに異種データの行が含まれている場合、1 つ以上のカテゴリのデータに対してフィルタ選択されたインデックスを作成できます。

たとえば、Production.Product テーブルに示される製品がそれぞれ ProductSubcategoryID に割り当てられ、Bikes、Components、Clothing、Accessories の製品カテゴリに関連付けられています。Production.Product テーブル内にあるこうしたカテゴリの列の値はあまり密接に関連していないので、異種カテゴリとなります。たとえば、Color、ReorderPoint、ListPrice、Weight、Class、および Style の特性は、各製品カテゴリに対して固有です。サブカテゴリ 27 ～ 36 を含む Accessories に対して頻繁に使用されるクエリがあるとします。Accessories のサブカテゴリにフィルタ選択されたインデックスを作成することで、Accessories に対するクエリのパフォーマンスを向上させることができます。

次の例では、Production.Product テーブルの Accessories のサブカテゴリに含まれるすべての製品にフィルタ選択されたインデックスを作成します。

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

フィルタ選択されたインデックス FIProductAccessories は次のクエリに対応します。これは、クエリ結果がインデックスに含まれ、クエリ プランにベース テーブルの参照が含まれないためです。たとえば、クエリ述語式 ProductSubcategoryID = 33 はフィルタ選択されたインデックスの述語 ProductSubcategoryID >= 27 および ProductSubcategoryID <= 36 のサブセットで、クエリ述語の ProductSubcategoryID 列と ListPrice 列はどちらもインデックスのキー列であり、名前は付加列としてインデックスのリーフ レベルに格納されます。

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

ビューは、クエリの定義を格納する仮想テーブルであり、フィルタ選択されたインデックスよりも幅広い目的と機能を備えています。ビューの詳細については、「ビューについて」および「ビューの使用に関するシナリオ」を参照してください。次の表では、ビューで使用できる一部の機能を、フィルタ選択されたインデックスの機能と比較しています。

*述語の単純な比較ロジックについては、「CREATE INDEX」の WHERE 句の構文を参照してください。

**述語の複雑な比較ロジックについては、「SELECT」の WHERE 句の構文を参照してください。

フィルタ選択されたインデックスをビューに作成することはできません。ただし、クエリ オプティマイザにとって、ビューで参照されているテーブルに定義されたフィルタ選択されたインデックスは役立ちます。クエリ オプティマイザでは、クエリ結果が正しくなる場合、ビューから選択するクエリに対してフィルタ選択されたインデックスが検討されます。次の例では、開始日が 2000 年 4 月 1 日より後のビューと、開始日が 2000 年 8 月 1 日より後のフィルタ選択されたインデックスを作成します。

USE AdventureWorks;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS 
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate 
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO

次の例では、クエリによって 2000 年 9 月 1 日より後の開始日が選択されます。この場合、開始日はすべて、フィルタ選択されたインデックスとフィルタを適用したビューに含まれます。フィルタ選択されたインデックス FIBillOfMaterialsByStartDate にはクエリの正しい結果が含まれるので、クエリ オプティマイザでは FIBillOfMaterialsByStartDate が検討されます。

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20000901';
GO

次の例では、クエリによって 2000 年 6 月 1 日より後の開始日が選択されます。この場合、開始日はすべて、ビューに含まれますが、フィルタ選択されたインデックスには含まれません。クエリ オプティマイザでは、フィルタ選択されたインデックス FIBillOfMaterialsByStartDate が検討されません。これは、クエリがビューから選択する際に正しい結果が返されるのと対照的に、フィルタ選択されたインデックスを使用すると、異なる結果が返される可能性があるためです。

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20000601';
GO

フィルタ選択されたインデックスは、インデックス付きビューよりも次の点で優れています。

• インデックスのメンテナンス コストの削減。たとえば、インデックス付きビューを更新する場合よりもフィルタ選択されたインデックスを更新する場合の方が、クエリ プロセッサで使用する CPU リソースが少なくなります。

• プランの品質の向上。たとえば、クエリのコンパイル時、同等のインデックス付きビューよりも多くの状況でフィルタ選択されたインデックスを使用することがクエリ オプティマイザで検討されます。

• オンラインでのインデックス再構築。フィルタ選択されたインデックスは、クエリで使用可能なときに再構築できます。オンラインでのインデックス再構築は、インデックス付きビューではサポートされていません。詳細については、「ALTER INDEX (Transact-SQL)」の REBUILD オプションの説明を参照してください。

• 一意ではないインデックス。フィルタ選択されたインデックスは一意ではないインデックスにすることができますが、インデックス付きビューは一意である必要があります。

上記の理由から、可能であれば、インデックス付きビューではなくフィルタ選択されたインデックスを使用することをお勧めします。インデックス付きビューではなくフィルタ選択されたインデックスを使用できるのは、ビューでテーブルを 1 つだけ参照する、クエリにより計算列が返されない、およびビューの述語で単純な比較ロジックを使用するという条件が満たされる場合です。たとえば、次の述語式は LIKE 演算子を含んでいるため、ビュー定義では使用できますが、フィルタ選択されたインデックスでは使用できません。

WHERE StartDate > '20000701' AND ModifiedDate LIKE 'E%'

フィルタ選択されたインデックスの定義に少数のキーまたは付加列を含めること、およびフィルタ選択されたインデックスをクエリ オプティマイザによってクエリ実行プランで選択するために必要な列だけを組み込むことをお勧めします。クエリ オプティマイザでは、フィルタ選択されたインデックスがクエリに対応するかどうかに関係なく、フィルタ選択されたインデックスがクエリに対して選択されます。ただし、フィルタ選択されたインデックスがクエリに対応する場合は、そのインデックスが選択される可能性は高くなります。クエリへの対応の詳細については、「付加列インデックスの作成」を参照してください。

場合によっては、フィルタ選択されたインデックスは、その式の列をキー列または付加列としてフィルタ選択されたインデックスの定義に含めなくても、クエリに対応します。次のガイドラインでは、フィルタ選択されたインデックスの式の列をフィルタ選択されたインデックスの定義でキー列または付加列にする必要がある場合について説明します。次の例では、以前に作成したフィルタ選択されたインデックス FIBillOfMaterialsWithEndDate を使用します。

フィルタ選択されたインデックスの式がクエリ述語と同じであり、フィルタ選択されたインデックスの式の列がクエリ結果と共に返されない場合、その式の列を、フィルタ選択されたインデックスの定義でキー列または付加列にする必要はありません。たとえば、クエリ述語がフィルタ式と同じであり、EndDate がクエリ結果と共に返されないため、FIBillOfMaterialsWithEndDate は次のクエリに対応します。FIBillOfMaterialsWithEndDate は、フィルタ選択されたインデックスの定義のキー列または付加列として EndDate を必要としません。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

フィルタ選択されたインデックスの式と異なるクエリ述語で比較に列が使用される場合は、フィルタ選択されたインデックスの式の列を、フィルタ選択されたインデックスの定義でキー列または付加列にする必要があります。たとえば、FIBillOfMaterialsWithEndDate は、フィルタ選択されたインデックスから行のサブセットを選択するので、次のクエリに対して有効です。ただし、EndDate が比較 EndDate > '20000101' で使用されるため、次のクエリには対応していません。この比較は、フィルタ選択されたインデックスの式と異なります。クエリ プロセッサでは、EndDate の値を参照せずにこのクエリを実行することはできません。したがって、EndDate をフィルタ選択されたインデックスの定義でキー列または付加列にする必要があります。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20000101';
GO

フィルタ選択されたインデックスの式の列がクエリ結果セットに含まれる場合、その列をフィルタ選択されたインデックスの定義でキー列または付加列にする必要があります。たとえば、FIBillOfMaterialsWithEndDate はクエリ結果に含まれる EndDate 列を返すので、次のクエリに対応しません。したがって、EndDate をフィルタ選択されたインデックスの定義でキー列または付加列にする必要があります。

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

テーブルの主キーは、フィルタ選択されたインデックスの定義でキー列または付加列にする必要はありません。主キーは、フィルタ選択されたインデックスなど、すべての非クラスタ化インデックスに自動的に含まれます。

フィルタ選択されたインデックスでは、その式に指定された比較演算子によって暗黙的または明示的なデータ変換が行われる場合、変換が比較演算子の左辺で行われると、エラーが発生します。解決方法としては、比較演算子の右辺にデータ変換演算子 (CAST または CONVERT) を含む、フィルタ選択されたインデックスの式を記述します。

次の例では、さまざまなデータ型が含まれるテーブルを作成します。

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO

次のフィルタ選択されたインデックスの定義では、列 b は、定数 1 と比較するために、整数データ型に暗黙的に変換されます。これにより、フィルタ選択された述語の演算子の左辺で変換が行われるため、エラー メッセージ 10611 が生成されます。

USE AdventureWorks;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

解決策として、次の例に示すように、右辺の定数を、列 b と同じ型になるように変換します。

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

データ変換を比較演算子の左辺から右辺に移動すると、変換の意味が変わることがあります。上記の例では、CONVERT 演算子を右側に追加したときに、整数の比較から varbinary の比較に変わりました。

sys.sql_expression_dependencies カタログ ビューでは、フィルタ選択されたインデックスの式の各列を、参照による依存関係として追跡します。フィルタ選択されたインデックスの式で定義されているテーブル列の定義を削除、名前変更、または変更することはできません。

フィルタ選択されたインデックスは、クエリが SELECT ステートメントで参照する、適切に定義されたデータのサブセットが列に含まれている場合に役立ちます。次に例を示します。

• NULL 以外の値を少数しか含まないスパース列。

• 複数のカテゴリのデータを含む異種列。

• 金額、時間、日付など、値の範囲を含む列。

• 列の値の単純な比較ロジックで定義されるテーブル パーティション。

フィルタ選択されたインデックスのメンテナンス コストの削減は、そのインデックスに含まれる行数がテーブル全体のインデックスと比較して少ない場合に、最も明確になります。フィルタ選択されたインデックスにテーブル内のほとんどの行が含まれる場合は、テーブル全体のインデックスよりもメンテナンス コストがかかることがあります。この場合は、フィルタ選択されたインデックスではなく、テーブル全体のインデックスを使用する必要があります。

フィルタ選択されたインデックスは 1 つのテーブルで定義され、単純な比較演算子のみをサポートします。複数のテーブルを参照するフィルタ式や複雑なロジックを含むフィルタ式が必要な場合は、ビューを作成する必要があります。

一般に、データベース エンジンとツールでは、フィルタ選択されたインデックスを特殊な非クラスタ化インデックスと見なすので、テーブル全体の非クラスタ化インデックスの場合と同じように、フィルタ選択されたインデックスをサポートします。フィルタ選択されたインデックスを完全にサポートする、サポートしない、またはサポートが制限されているツールと機能に関する注意事項を次に示します。

• ALTER INDEX はフィルタ選択されたインデックスをサポートします。フィルタ選択されたインデックスの式を変更するには、CREATE INDEX WITH DROP_EXISTING を使用します。

• 欠落したインデックス機能では、フィルタ選択されたインデックスを推奨しません。

• データベース エンジン チューニング アドバイザでは、インデックス チューニング アドバイスの推奨時に、フィルタ選択されたインデックスが検討されます。フィルタ選択されたインデックス is not null が推奨されることもあります。

• オンライン インデックス操作では、フィルタ選択されたインデックスがサポートされます。

• テーブル ヒントでは、フィルタ選択されたインデックスがサポートされますが、これ以外のインデックスには適用されない制限がいくつかあります。これらの制限については、次のセクションで説明します。

パラメータ化クエリには、コンパイル時に、クエリ オプティマイザがフィルタ選択されたインデックスを選択するのに十分な情報が含まれていない場合もあります。クエリを書き直して不足情報を提供することができる場合があります。次の例では、@p および @q のパラメータ値がコンパイル時に確定していないため、クエリ オプティマイザでは、SELECT ステートメントに対してフィルタ選択されたインデックス FIBillOfMaterialsWithComponentID を検討しません。次に示すクエリの例は、SHOWPLAN_XML が ON に設定された状態で実行されるので、パラメータ化クエリの一致しないフィルタ選択されたインデックスを SHOWPLAN_XML 出力に表示できます。

USE AdventureWorks;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials 
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO

SHOWPLAN_XML 出力の UnmatchedIndexes 要素と Parameterization サブ要素は、フィルタ選択されたインデックスがクエリに一致しなかったことを示します。SHOWPLAN_XML 出力の表示方法については、「XML プラン表示」を参照してください。

この問題を解決するには、パラメータ化された式がフィルタ述語のサブセットではない場合にクエリ結果が空になるように、クエリを変更します。次のクエリでは、この変更を示します。WHERE 句に ComponentID in (533, 324, 753) 式を追加すると、クエリ結果は確実にフィルタ選択された述語式のサブセットになります。この変更により、クエリ オプティマイザは、次の SELECT ステートメントに対してフィルタ選択されたインデックス FIBillOfMaterialsWithComponentID を検討できるようになります。

USE AdventureWorks;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

簡易パラメータ化

クエリ オプティマイザでは、クエリに対応しない場合でも、キー参照を実行して、フィルタ選択されたインデックスが対応しない残りの列を取得することで、フィルタ選択されたインデックスを使用できます。キー参照の詳細については、「Key Lookup Showplan Operator」を参照してください。キー参照の推定数が少ない場合、クエリ オプティマイザでは、この方法が選択されることがあります。次のクエリでは、インデックス ヒントを使用し、EndDate のブックマーク参照と共に FIBillOfMaterialsWithEndDate をクエリ プロセッサで使用します。キー参照は、クエリ述語の EndDate > @date 比較に対して行われます。

USE AdventureWorks;
GO
DECLARE @date AS DATE;
SET @date = '20000825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

EndDate > @Date はフィルタ選択されたインデックスの式 EndDate IS NOT NULL と完全一致ではないことに注意してください。フィルタ選択されたインデックスの式で定義された行のサブセットが返されるので、フィルタ選択されたインデックスは、このパラメータ化クエリに対して引き続き有効です。