フィルター選択されたインデックスの作成

  • [アーティクル]

このトピックでは、SQL Server 2012 で SQL Server Management Studio または Transact-SQL を使用して、フィルター選択されたインデックスを作成する方法について説明します。 フィルター選択されたインデックスは、最適化された非クラスター化インデックスであり、適切に定義されたデータのサブセットから選択するクエリに対応する際に特に適しています。 フィルター選択されたインデックスは、フィルター述語を使用して、テーブル内の一部の行にインデックスを作成します。 フィルター選択されたインデックスを適切にデザインすると、クエリのパフォーマンスが向上するだけでなく、テーブル全体のインデックスと比較してインデックスのメンテナンス コストおよびストレージ コストを削減できます。

フィルター選択されたインデックスは、テーブル全体のインデックスよりも次の点で優れています。

  • クエリのパフォーマンスとプランの品質の向上

    フィルター選択されたインデックスを適切にデザインすると、クエリのパフォーマンスと実行プランの品質が向上します。これは、このインデックスが、テーブル全体の非クラスター化インデックスよりも小さく、フィルター選択された統計情報を含むためです。 フィルター選択された統計情報は、フィルター選択されたインデックスの行のみを対象としているため、テーブル全体の統計情報よりも正確です。

  • インデックスのメンテナンス コストの削減

    インデックスのメンテナンスが行われるのは、データ操作言語 (DML) ステートメントがインデックス内のデータに影響を与える場合のみです。 フィルター選択されたインデックスにより、インデックスのメンテナンス コストは、テーブル全体の非クラスター化インデックスと比較して削減されます。これは、フィルター選択されたインデックスは小さく、インデックス内のデータが変更された場合にのみメンテナンスされるためです。 特に、含まれるデータの変更頻度が引く場合は、多数のフィルター選択されたインデックスを作成できます。 同様に、フィルター選択されたインデックスに頻繁に変更されるデータのみが含まれている場合は、インデックスのサイズを小さくすると、統計情報の更新コストが削減されます。

  • インデックスのストレージ コストの削減

    テーブル全体のインデックスが不要な場合は、フィルター選択されたインデックスを作成すると、非クラスター化インデックスのディスク ストレージを削減できます。 ストレージ要件をあまり増やすことなく、テーブル全体の非クラスター化インデックスを複数のフィルター選択されたインデックスに置き換えることができます。

このトピックの内容

  • 作業を開始する準備:

    デザインに関する考慮事項

    制限事項と制約事項

    セキュリティ

  • 以下を使用してフィルター選択されたインデックスを作成するには:

    SQL Server Management Studio

    Transact-SQL

作業を開始する準備

デザインに関する考慮事項

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

  • テーブルに異種データの行が含まれている場合、1 つ以上のカテゴリのデータに対してフィルター選択されたインデックスを作成できます。 これにより、クエリのフォーカスをテーブルの特定の領域に狭めて、これらのデータに対するクエリのパフォーマンスを向上させることができます。 繰り返しになりますが、作成したインデックスは、テーブル全体の非クラスター化インデックスよりも小さく、メンテナンス コストが少なくなります。

制限事項と制約事項

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

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

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

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

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

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

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

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

  • フィルター選択されたインデックスの式と異なるクエリ述語で比較に列が使用される場合は、フィルター選択されたインデックスの式の列を、フィルター選択されたインデックスの定義でキー列または付加列にする必要があります。

  • フィルター選択されたインデックスの式の列がクエリ結果セットに含まれる場合、その列をフィルター選択されたインデックスの定義でキー列または付加列にする必要があります。

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

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

セキュリティ

権限

テーブルまたはビューに対する ALTER 権限が必要です。 実行するには、固定サーバー ロール sysadmin または、固定データベース ロール db_ddladmin および db_owner のメンバーである必要があります。 フィルター選択されたインデックスの式を変更するには、CREATE INDEX WITH DROP_EXISTING を使用します。

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

SQL Server Management Studio の使用

フィルター選択されたインデックスを作成するには

  1. オブジェクト エクスプローラーで、フィルター選択されたインデックスを作成するテーブルが格納されているデータベースをプラス記号をクリックして展開します。

  2. プラス記号をクリックして [テーブル] フォルダーを展開します。

  3. プラス記号をクリックして、フィルター選択されたインデックスを作成するテーブルを展開します。

  4. [インデックス] フォルダーを右クリックし、[新しいインデックス] をポイントし、[非クラスター化インデックス] を選択します。

  5. [新しいインデックス] ダイアログ ボックスの [全般] ページで、[インデックス名] ボックスに新しいインデックスの名前を入力します。

  6. [インデックス キー列] で、[追加] をクリックします。

  7. [table_name から列を選択] ダイアログ ボックスで、一意なインデックスに追加する 1 つまたは複数のテーブル列のチェック ボックスをオンにします。

  8. [OK] をクリックします。

  9. [フィルター] ページで、[フィルター式] に、フィルター選択されたインデックスの作成に使用する SQL 式を入力します。

  10. [OK] をクリックします。

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

Transact-SQL の使用

フィルター選択されたインデックスを作成するには

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

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

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、[実行] をクリックします。

    USE AdventureWorks2012;
GO
-- Looks for an existing filtered index named "FIBillOfMaterialsWithEndDate"
-- and deletes it from the table Production.BillOfMaterials if found. 
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
-- Creates a filtered index "FIBillOfMaterialsWithEndDate"
-- on the table Production.BillOfMaterials 
-- using the columms ComponentID and StartDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

    上記のフィルター選択されたインデックスは、次のクエリに対して有効です。 クエリ実行プランを表示して、クエリ オプティマイザーでフィルター選択されたインデックスが使用されたかどうかを確認できます。

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

フィルター選択されたインデックスが SQL クエリで使用されるようにするには

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

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

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、[実行] をクリックします

    USE AdventureWorks2012;
GO
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) ) 
WHERE EndDate IN ('20000825', '20000908', '20000918'); 
GO

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

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