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

適用対象: ○SQL Server ○Azure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

このトピックでは、 SQL Server 2017SQL Server 2017[SQL Server Management Studio]SQL Server Management Studio または Transact-SQLTransact-SQLを使用して、フィルター選択されたインデックスを作成する方法について説明します。This topic describes how to create a filtered index in SQL Server 2017SQL Server 2017 by using [SQL Server Management Studio]SQL Server Management Studio or Transact-SQLTransact-SQL. フィルター選択されたインデックスは、最適化された非クラスター化インデックスであり、適切に定義されたデータのサブセットから選択するクエリに対応する際に特に適しています。A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. フィルター選択されたインデックスは、フィルター述語を使用して、テーブル内の一部の行にインデックスを作成します。It uses a filter predicate to index a portion of rows in the table. フィルター選択されたインデックスを適切にデザインすると、クエリのパフォーマンスが向上するだけでなく、テーブル全体のインデックスと比較してインデックスのメンテナンス コストおよびストレージ コストを削減できます。A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

フィルター選択されたインデックスは、テーブル全体のインデックスよりも次の点で優れています。Filtered indexes can provide the following advantages over full-table indexes:

  • クエリのパフォーマンスとプランの品質の向上Improved query performance and plan quality

    フィルター選択されたインデックスを適切にデザインすると、クエリのパフォーマンスと実行プランの品質が向上します。これは、このインデックスが、テーブル全体の非クラスター化インデックスよりも小さく、フィルター選択された統計情報を含むためです。A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. フィルター選択された統計情報は、フィルター選択されたインデックスの行のみを対象としているため、テーブル全体の統計情報よりも正確です。The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • インデックスのメンテナンス コストの削減Reduced index maintenance costs

    インデックスのメンテナンスが行われるのは、データ操作言語 (DML) ステートメントがインデックス内のデータに影響を与える場合のみです。An index is maintained only when data manipulation language (DML) statements affect the data in the index. フィルター選択されたインデックスにより、インデックスのメンテナンス コストは、テーブル全体の非クラスター化インデックスと比較して削減されます。これは、フィルター選択されたインデックスは小さく、インデックス内のデータが変更された場合にのみメンテナンスされるためです。A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is changed. 特に、含まれるデータの変更頻度が引く場合は、多数のフィルター選択されたインデックスを作成できます。It is possible to have a large number of filtered indexes, especially when they contain data that is changed infrequently. 同様に、フィルター選択されたインデックスに頻繁に変更されるデータのみが含まれている場合は、インデックスのサイズを小さくすると、統計情報の更新コストが削減されます。Similarly, if a filtered index contains only the frequently modified data, the smaller size of the index reduces the cost of updating the statistics.

  • インデックスのストレージ コストの削減Reduced index storage costs

    テーブル全体のインデックスが不要な場合は、フィルター選択されたインデックスを作成すると、非クラスター化インデックスのディスク ストレージを削減できます。Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. ストレージ要件をあまり増やすことなく、テーブル全体の非クラスター化インデックスを複数のフィルター選択されたインデックスに置き換えることができます。You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

このトピックの内容In This Topic

はじめにBefore You Begin

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

  • クエリに関連する少数の値だけが列に含まれている場合、値のサブセットにフィルター選択されたインデックスを作成できます。When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. たとえば、列の値がほとんど NULL の場合に、クエリで常に NULL 以外の値を選択するときは、NULL 以外のデータ行にフィルター選択されたインデックスを作成できます。For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. 作成したインデックスは、同じキー列に定義されているテーブル全体の非クラスター化インデックスよりも小さく、メンテナンス コストが少なくなります。The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

  • テーブルに異種データの行が含まれている場合、1 つ以上のカテゴリのデータに対してフィルター選択されたインデックスを作成できます。When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data. これにより、クエリのフォーカスをテーブルの特定の領域に狭めて、これらのデータに対するクエリのパフォーマンスを向上させることができます。This can improve the performance of queries on these data rows by narrowing the focus of a query to a specific area of the table. 繰り返しになりますが、作成したインデックスは、テーブル全体の非クラスター化インデックスよりも小さく、メンテナンス コストが少なくなります。Again, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index.

制限事項と制約事項Limitations and Restrictions

  • フィルター選択されたインデックスをビューに作成することはできません。You cannot create a filtered index on a view. ただし、クエリ オプティマイザーにとって、ビューで参照されているテーブルに定義されたフィルター選択されたインデックスは役立ちます。However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. クエリ オプティマイザーでは、クエリ結果が正しくなる場合、ビューから選択するクエリに対してフィルター選択されたインデックスが検討されます。The query optimizer considers a filtered index for a query that selects from a view if the query results will be correct.

  • フィルター式でアクセスされる列が CLR データ型の場合、テーブルにフィルター選択されたインデックスを作成することはできません。You cannot create a filtered index on a table when the column accessed in the filter expression is of a CLR data type.

  • フィルター選択されたインデックスは、インデックス付きビューよりも次の点で優れています。Filtered indexes have the following advantages over indexed views:

    • インデックスのメンテナンス コストの削減。Reduced index maintenance costs. たとえば、インデックス付きビューを更新する場合よりもフィルター選択されたインデックスを更新する場合の方が、クエリ プロセッサで使用する CPU リソースが少なくなります。For example, the query processor uses fewer CPU resources to update a filtered index than an indexed view.

    • プランの品質の向上。Improved plan quality. たとえば、クエリのコンパイル時、同等のインデックス付きビューよりも多くの状況でフィルター選択されたインデックスを使用することがクエリ オプティマイザーで検討されます。For example, during query compilation, the query optimizer considers using a filtered index in more situations than the equivalent indexed view.

    • オンラインでのインデックス再構築。Online index rebuilds. フィルター選択されたインデックスは、クエリで使用可能なときに再構築できます。You can rebuild filtered indexes while they are available for queries. オンラインでのインデックス再構築は、インデックス付きビューではサポートされていません。Online index rebuilds are not supported for indexed views. 詳細については、「ALTER INDEX (Transact-SQL)」の「REBUILD オプション」を参照してください。For more information, see the REBUILD option for ALTER INDEX (Transact-SQL).

    • 一意ではないインデックス。Non-unique indexes. フィルター選択されたインデックスは一意ではないインデックスにすることができますが、インデックス付きビューは一意である必要があります。Filtered indexes can be non-unique, whereas indexed views must be unique.

  • フィルター選択されたインデックスは 1 つのテーブルで定義され、単純な比較演算子のみをサポートします。Filtered indexes are defined on one table and only support simple comparison operators. 複数のテーブルを参照するフィルター式や複雑なロジックを含むフィルター式が必要な場合は、ビューを作成する必要があります。If you need a filter expression that references multiple tables or has complex logic, you should create a view.

  • フィルター選択されたインデックスの式がクエリ述語と同じであり、フィルター選択されたインデックスの式の列がクエリ結果と共に返されない場合、その式の列を、フィルター選択されたインデックスの定義でキー列または付加列にする必要はありません。A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results.

  • フィルター選択されたインデックスの式と異なるクエリ述語で比較に列が使用される場合は、フィルター選択されたインデックスの式の列を、フィルター選択されたインデックスの定義でキー列または付加列にする必要があります。A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.

  • フィルター選択されたインデックスの式の列がクエリ結果セットに含まれる場合、その列をフィルター選択されたインデックスの定義でキー列または付加列にする必要があります。A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.

  • テーブルのクラスター化インデックス キーは、フィルター選択されたインデックスの定義でキー列または付加列にする必要はありません。The clustered index key of the table does not need to be a key or included column in the filtered index definition. クラスター化インデックス キーは、フィルター選択されたインデックスなど、すべての非クラスター化インデックスに自動的に含まれます。The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

  • フィルター選択されたインデックスでは、その式に指定された比較演算子によって暗黙的または明示的なデータ変換が行われる場合、変換が比較演算子の左辺で行われると、エラーが発生します。If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. 解決方法としては、比較演算子の右辺にデータ変換演算子 (CAST または CONVERT) を含む、フィルター選択されたインデックスの式を記述します。A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

  • CREATE INDEX (Transact-SQL) 構文のフィルター選択されたインデックスの作成に必要な SET オプションを確認します。Review the required SET options for filtered index creation in CREATE INDEX (Transact-SQL) syntax

セキュリティSecurity

PermissionsPermissions

テーブルまたはビューに対する ALTER 権限が必要です。Requires ALTER permission on the table or view. 実行するには、 sysadmin 固定サーバー ロール、または db_ddladmin 固定データベース ロールおよび db_owner 固定データベース ロールのメンバーである必要があります。User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles. フィルター選択されたインデックスの式を変更するには、CREATE INDEX WITH DROP_EXISTING を使用します。To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.

SQL Server Management Studio の使用Using SQL Server Management Studio

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

  1. オブジェクト エクスプローラーで、フィルター選択されたインデックスを作成するテーブルが格納されているデータベースをプラス記号をクリックして展開します。In Object Explorer, click the plus sign to expand the database that contains the table on which you want to create a filtered index.

  2. プラス記号をクリックして [テーブル] フォルダーを展開します。Click the plus sign to expand the Tables folder.

  3. プラス記号をクリックして、フィルター選択されたインデックスを作成するテーブルを展開します。Click the plus sign to expand the table on which you want to create a filtered index.

  4. [インデックス] フォルダーを右クリックし、[新しいインデックス] をポイントし、[非クラスター化インデックス] を選択します。Right-click the Indexes folder, point to New Index, and select Non-Clustered Index....

  5. [新しいインデックス] ダイアログ ボックスの [全般] ページで、 [インデックス名] ボックスに新しいインデックスの名前を入力します。In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

  6. [インデックス キー列] で、[追加] をクリックします。Under Index key columns, click Add....

  7. [table_name から列を選択] ダイアログ ボックスで、一意のインデックスに追加する 1 つまたは複数のテーブル列のチェック ボックスをオンにします。In the Select Columns fromtable_name dialog box, select the check box or check boxes of the table column or columns to be added to the unique index.

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

  9. [フィルター] ページで、[フィルター式] に、フィルター選択されたインデックスの作成に使用する SQL 式を入力します。On the Filter page, under Filter Expression, enter SQL expression that you'll use to create the filtered index.

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

Transact-SQL の使用Using Transact-SQL

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

  1. オブジェクト エクスプローラーで、 データベース エンジンDatabase Engineのインスタンスに接続します。In Object Explorer, connect to an instance of データベース エンジンDatabase Engine.

  2. [標準] ツール バーの [新しいクエリ] をクリックします。On the Standard bar, click New Query.

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] をクリックします。Copy and paste the following example into the query window and click Execute.

    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  
    

    上記のフィルター選択されたインデックスは、次のクエリに対して有効です。The filtered index above is valid for the following query. クエリ実行プランを表示して、クエリ オプティマイザーでフィルター選択されたインデックスが使用されたかどうかを確認できます。You can display the query execution plan to determine if the query optimizer used the filtered index.

    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 クエリで使用されるようにするにはTo ensure that a filtered index is used in a SQL query

  1. オブジェクト エクスプローラーで、 データベース エンジンDatabase Engineのインスタンスに接続します。In Object Explorer, connect to an instance of データベース エンジンDatabase Engine.

  2. [標準] ツール バーの [新しいクエリ] をクリックします。On the Standard bar, click New Query.

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] をクリックします。Copy and paste the following example into the query window and click Execute.

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

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