CREATE INDEX (Transact-SQL)

  • [アーティクル]

更新 : 2006 年 4 月 14 日

指定したテーブルまたはビューにリレーショナル インデックスを作成するか、指定したテーブルに XML インデックスを作成します。インデックスはテーブル内にデータがなくても作成できます。データベース名を修飾して指定することにより、他のデータベース内のテーブルまたはビューにインデックスを作成することもできます。

トピック リンク アイコンTransact-SQL 構文表記規則

構文

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Create XML Index 
CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Backward Compatible Relational Index
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) 
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
        table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

引数

  • UNIQUE
    テーブルまたはビューに一意なインデックスを作成します。一意なインデックスとは、どの 2 つの行にも同じインデックス キー値が設定されていないインデックスです。ビューのクラスタ化インデックスは一意であることが必要です。

    SQL Server 2005 データベース エンジンでは、IGNORE_DUP_KEY が ON に設定されているかどうかに関係なく、重複する値が既に含まれる列に対して一意なインデックスを作成することはできません。作成しようとすると、データベース エンジンではエラー メッセージが表示されます。1 行または複数行に対して一意なインデックスを作成するには、先に重複する値を削除しておく必要があります。一意なインデックスに使用する列は NOT NULL に設定してください。一意なインデックスを作成するとき、複数の NULL 値は重複した値と見なされます。

  • CLUSTERED
    インデックスを作成します。この場合、テーブル内にある、インデックスに対応する行の物理的な順序は、キー値の論理的順序によって決まります。クラスタ化インデックスの最下位レベル (リーフ レベル）には、テーブルの実際のデータ行が含まれます。1 つのテーブルまたはビューに、同時に複数のクラスタ化インデックスを定義することはできません。詳細については、「クラスタ化インデックスの構造」を参照してください。

    一意なクラスタ化インデックスが定義されているビューは、インデックス付きビューと呼ばれます。ビューに一意なクラスタ化インデックスを作成すると、ビューを物理的に具体化することになります。ビューにその他のインデックスを定義するには、まずそのビューに一意のクラスタ化インデックスを作成する必要があります。詳細については、「インデックス付きビューのデザイン」を参照してください。

    非クラスタ化インデックスを作成する前に、クラスタ化インデックスを作成します。これは、クラスタ化インデックスを作成すると、テーブルの既存の非クラスタ化インデックスが再構築されるためです。

    CLUSTERED を指定しない場合、非クラスタ化インデックスが作成されます。

    ms188783.note(ja-jp,SQL.90).gifメモ :
    クラスタ化インデックスのリーフ レベルとデータ ページは定義が同じであるため、クラスタ化インデックスを作成して、ON partition_scheme_name または ON filegroup_name 句を使用すると、テーブルが作成されたファイル グループから新しいパーティション構成またはファイル グループに、テーブルを効率的に移動できます。特定のファイル グループ上にテーブルまたはインデックスを作成する前に、使用できるファイル グループ、およびインデックス用の十分な空領域を確認しておいてください。詳細については、「インデックスに必要なディスク領域の決定」を参照してください。

  • NONCLUSTERED
    テーブルの論理順序を示すインデックスを作成します。非クラスタ化インデックスの場合、データ行の物理的な順序は、そのインデックスが作成された順序には依存しません。詳細については、「非クラスタ化インデックスの構造」を参照してください。

    インデックスの作成方法に関係なく、暗黙的に PRIMARY KEY および UNIQUE 制約を使用する場合も、明示的に CREATE INDEX を使用する場合も、各テーブルには 249 個までの非クラスタ化インデックスを作成できます。

    インデックス付きビューの場合は、既に一意なクラスタ化インデックスが作成されているビューにのみ、非クラスタ化インデックスを作成できます。

    既定値は NONCLUSTERED です。

  • index_name
    インデックスの名前を指定します。インデックス名は、テーブルまたはビュー内では一意であることが必要ですが、データベース内では一意である必要はありません。インデックス名は、識別子の規則に従っている必要があります。

    プライマリ XML インデックス名は、#、##、@、または @@ で始めることはできません。

  • column
    インデックスの基準となる 1 列または複数列を指定します。指定した列を組み合わせた値で複合インデックスを作成するには、2 つ以上の列名を指定してください。複合インデックスに含まれる列は、table_or_view_name の後のかっこ内に、並べ替えの優先順序に従って指定します。

    16 列までを、1 つの複合インデックス キーとして結合できます。複合インデックス キーに含まれる列はすべて、同じテーブルまたはビューに存在する必要があります。複合インデックスの最大許可サイズは、900 バイトです。複合インデックスの可変型列の詳細については、「解説」を参照してください。

    ラージ オブジェクト (LOB) データ型 (ntext、text、varchar(max)、 nvarchar(max)、varbinary(max)、xml、または image) の列は、インデックスのキー列として指定できません。また、ntext、text、または image 型の列は、CREATE INDEX ステートメントで参照されていなくても、ビュー定義に含めることはできません。

    データ型でバイナリ順序がサポートされる場合は、CLR ユーザー定義型列に対してインデックスを作成できます。ユーザー定義型列でメソッドの呼び出しとして定義されている計算列にも、そのメソッドが決定的とマークされており、データ アクセス操作が実行されない限り、インデックスを作成できます。CLR ユーザー定義型列でのインデックス作成の詳細については、「CLR ユーザー定義型の使用」を参照してください。

  • [ ASC | DESC ]
    特定のインデックス列に対して、昇順または降順の並べ替えの方向を指定します。既定値は ASC です。

  • INCLUDE **(**column [ ,... n ] )
    非クラスタ化インデックスのリーフ レベルに、非キー列を追加します。非クラスタ化インデックスは、一意であっても一意でなくてもかまいません。

    追加される非キー列の最大数は 1,023 列で、最小数は 1 列です。

    列名は INCLUDE リスト内で繰り返すことはできず、キー列と非キー列両方で同時に使用することはできません。詳細については、「付加列インデックス」を参照してください。

    text、ntext、および image 型を除く、すべてのデータ型が許可されます。指定する非キー列のいずれか 1 つが varchar(max)、nvarchar(max)、または varbinary(max) 型の場合、インデックスはオフライン (ONLINE = OFF) で作成または再構築する必要があります。

    決定的であり、かつ正確または不正確のいずれかである計算列は、付加列にすることができます。image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、および xml 型から派生した計算列は、その計算列のデータ型が付加列として許可されている限り、非キーの付加列にできます。詳細については、「計算列に対するインデックスの作成」を参照してください。

  • ON partition_scheme_name**(column_name)**
    ファイル グループが定義されているパーティション構成を指定します。このファイル グループは、パーティション インデックスのパーティションのマップ先となります。パーティション構成は、CREATE PARTITION SCHEME または ALTER PARTITION SCHEME のいずれかを実行することにより、データベース内に存在する必要があります。column_name には、パーティション インデックスのパーティション分割で対象となる列を指定します。この列は、partition_scheme_name で使用されているパーティション関数の引数のデータ型、長さ、および有効桁数に一致する必要があります。column_name は、インデックス定義で指定されている列以外でも問題ありません。UNIQUE インデックスをパーティション分割する場合、column_name は一意なキーとして使用されている列から選択する必要がありますが、それ以外の場合はベース テーブルの任意の列を指定できます。この制限により、データベース エンジンでは、単一のパーティション内だけでキー値の一意性を確認できます。

    ms188783.note(ja-jp,SQL.90).gifメモ :
    一意でないクラスタ化インデックスをパーティション分割するとき、データベース エンジンでは既定により、まだ指定されていないパーティション分割列がクラスタ化インデックス キーのリストに追加されます。一意でない非クラスタ化インデックスをパーティション分割するとき、データベース エンジンでは、まだ指定されていないパーティション分割列がインデックスの非キー列 (付加列) として追加されます。

    partition_scheme_name または filegroup を指定せず、テーブルがパーティション分割される場合、インデックスは基になるテーブルと同じパーティション分割列を使って、同じパーティション構造に配置されます。

    XML インデックスにはパーティション構成を指定できません。ベース テーブルがパーティション分割される場合、XML インデックスではテーブルと同じパーティション構造が使用されます。

    パーティション インデックスの詳細については、「パーティション インデックスの専用ガイドライン」を参照してください。

  • ON filegroup_name
    指定したファイル グループに、指定したインデックスを作成します。位置を指定せず、テーブルまたはビューがパーティション分割されていない場合、インデックスでは、基になるテーブルまたはビューと同じファイル グループが使用されます。ファイル グループは既に存在している必要があります。XML インデックスではテーブルと同じファイル グループが使用されます。

  • ON "default"
    既定のファイル グループに、指定したインデックスを作成します。

    この文脈での default という語はキーワードではありません。これは、既定のファイル グループの識別子で、ON "default" または ON [default] のように区切り記号で区切る必要があります。"default" を指定する場合は、現在のセッションに対して QUOTED_IDENTIFIER オプションが ON になっている必要があります。これは既定の設定です。詳細については、「SET QUOTED_IDENTIFIER (Transact-SQL)」を参照してください。

  • [PRIMARY] XML
    指定した xml 列に、XML インデックスを作成します。PRIMARY を指定した場合、クラスタ化インデックスは、ユーザー テーブルのクラスタ化キーと XML ノード識別子から構成されるクラスタ化キーで作成されます。各テーブルには 249 個までの XML インデックスを作成できます。XML インデックスを作成するときには、次のことに注意してください。

    • クラスタ化インデックスは、ユーザー テーブルの主キー上に存在する必要がある。
    • ユーザー テーブルのクラスタ化キーは 15 列に制限されている。
    • テーブル内の各 xml 列には、1 つのプライマリ XML インデックスと、複数のセカンダリ XML インデックスを作成できる。
    • xml 列にセカンダリ XML インデックスを作成するには、プライマリ XML インデックスが存在している必要がある。
    • XML インデックスは、単一の xml 列にのみ作成できる。XML インデックスは xml 以外の列には作成できません。またリレーショナル インデックスは xml 列には作成できません。
    • ビュー内の xml 列、xml 列を使用したテーブル値変数、および xml 型の変数には、プライマリまたはセカンダリのいずれの XML インデックスも作成できない。
    • xml 計算列にはプライマリ XML インデックスは作成できない。
    • SET オプション設定は、インデックス付きビューおよび計算列インデックスに必要な設定と同じにする必要がある。特に、XML インデックスを作成し、xml 列に値を挿入、削除、更新する場合は、オプション ARITHABORT が ON に設定されている必要があります。詳細については、「結果に影響を与える SET オプション」を参照してください。

    詳細については、「xml データ型列のインデックス」を参照してください。

  • xml_column_name
    インデックスの基準となる xml 列を指定します。単一の XML インデックス定義には、1 つの xml 列しか指定できませんが、1 つの xml 列には複数のセカンダリ XML インデックスを作成できます。
  • USING XML INDEX xml_index_name
    セカンダリ XML インデックスの作成でプライマリ XML インデックスを使用します。

  • FOR { VALUE | PATH | PROPERTY }
    セカンダリ XML インデックスの種類を指定します。

    • VALUE
      キー列がプライマリ XML インデックス (ノード値とパス) となっている列に、セカンダリ XML インデックスを作成します。
    • PATH
      プライマリ XML インデックスのパス値とノード値に基づく列に、セカンダリ XML インデックスを作成します。PATH セカンダリ インデックスでは、パス値とノード値は、パスの検索に役立つキー列となります。
    • PROPERTY
      PK がベース テーブルの主キーとなっているプライマリ XML インデックスの列 (PK、パスおよびノード値) に、セカンダリ XML インデックスを作成します。
<object>::=

インデックスを作成するオブジェクトを、完全修飾または完全修飾ではない形式で指定します。

  • database_name
    データベースの名前を指定します。
  • schema_name
    テーブルまたはビューが属するスキーマの名前を指定します。

  • table_or_view_name
    インデックスを作成するテーブルまたはビューの名前を指定します。

    ビューにインデックスを作成するには、SCHEMABINDING を指定してそのビューを定義する必要があります。ビューに非クラスタ化インデックスを作成する前に、そのビューに一意のクラスタ化インデックスを作成する必要があります。インデックス付きビューの詳細については、「解説」を参照してください。

<relational_index_option>::=

インデックスを作成するときに使用するオプションを指定します。

  • PAD_INDEX = { ON | OFF }
    インデックスの埋め込みを指定します。既定値は OFF です。

    • ON
      fillfactor で指定される空き領域のパーセンテージが、インデックスの中間レベルのページに適用されます。
    • OFF または fillfactor の指定なし
      中間レベルのページはほぼ全容量が使用されます。ただし、中間ページにあるキーのセットを考慮して、インデックスに割り当てることのできる、少なくとも 1 行の最大サイズが収まる分の領域は残されます。

    PAD_INDEX では FILLFACTOR で指定されるパーセンテージが使用されるので、PAD_INDEX オプションは、FILLFACTOR が指定されている場合にのみ有効です。FILLFACTOR で指定されるパーセンテージで 1 行分のデータを格納できない場合、データベース エンジンでは内部的に、最小サイズを格納できるパーセンテージが使用されます。中間インデックス ページの行数は、fillfactor の値がどれだけ小さくなっても 2 未満にはなりません。

    旧バージョンと互換性のある構文では、WITH PAD_INDEX は WITH PAD_INDEX = ON と同じです。

  • FILLFACTOR **=**fillfactor
    インデックスの作成時や再構築時に、データベース エンジンが各インデックス ページのリーフ レベルをどのくらい使用するかを示すパーセンテージを指定します。fillfactor には 1 ～ 100 の整数値を指定する必要があります。既定値は 0 です。fillfactor が 100 または 0 の場合、データベース エンジンでは全容量を使用するリーフ ページでインデックスが作成されます。

    ms188783.note(ja-jp,SQL.90).gifメモ :
    FILL FACTOR 値 0 と 100 の機能は、まったく同じです。

    FILLFACTOR 設定は、インデックスが作成または再構築されるときのみ適用されます。データベース エンジンでは、ページ内で指定されたパーセント分の空領域は動的に保持されません。FILL FACTOR 設定を表示するには、sys.indexes カタログ ビューを使用します。

    ms188783.note(ja-jp,SQL.90).gif重要 :
    データベース エンジンでは、クラスタ化インデックスの作成時にデータが再分配されるため、100 未満の FILLFACTOR 値を使ってクラスタ化インデックスを作成すると、データ用のストレージ領域のサイズに影響が生じます。

    詳細については、「FILL FACTOR」を参照してください。

  • SORT_IN_TEMPDB = { ON | OFF }
    tempdb に一時的な並べ替え結果を格納するかどうかを指定します。既定値は OFF です。

    • ON
      インデックス構築に使用される中間の並べ替え結果が tempdb に格納されます。この場合、tempdb がユーザー データベースとは別のディスク セット上に存在すると、インデックスの作成にかかる時間を短縮できますが、インデックスの構築中に使用されるディスク領域のサイズは増加します。
    • OFF
      中間の並べ替え結果はインデックスと同じデータベースに格納されます。

    インデックスを作成するためユーザー データベース内に必要となる領域の他、tempdb には、並べ替えの中間結果を格納するためほぼ同じ大きさの追加領域が必要になります。詳細については、「tempdb とインデックスの作成」を参照してください。

    旧バージョンと互換性のある構文では、WITH SORT_IN_TEMPDB は WITH SORT_IN_TEMPDB = ON と同じです。

  • IGNORE_DUP_KEY = { ON | OFF }
    一意のクラスタ化インデックスまたは一意の非クラスタ化インデックスにおいて、複数行の挿入操作で重複したキー値が見つかった場合の、エラー応答を指定します。既定値は OFF です。

    • ON
      警告メッセージが発行され、一意なインデックスに違反する行のみが失敗します。
    • OFF
      エラー メッセージが発行され、INSERT トランザクション全体がロールバックされます。

    IGNORE_DUP_KEY 設定は、インデックスが作成または再構築された後に行われる挿入操作のみに適用されます。インデックス作成操作中は、この設定は影響しません。

    XML インデックスと、ビューで作成されるインデックスに対しては、IGNORE_DUP_KEY は ON に設定できません。

    旧バージョンと互換性のある構文では、WITH IGNORE_DUP_KEY は WITH IGNORE_DUP_KEY = ON と同じです。

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    分布統計を再計算するかどうかを指定します。既定値は OFF です。

    • ON
      古い統計情報は、自動的には再計算されません。
    • OFF
      自動統計更新が有効です。

    自動統計更新を復元するには、STATISTICS_NORECOMPUTE を OFF に設定するか、NORECOMPUTE 句を指定せずに UPDATE STATISTICS を実行します。

    ms188783.note(ja-jp,SQL.90).gif重要 :
    分布統計の自動再計算を無効にすると、クエリ オプティマイザで、テーブルに関連するクエリの最適実行プランが選択されなくなる場合があります。

    旧バージョンと互換性のある構文では、WITH STATISTICS_NORECOMPUTE は WITH STATISTICS_NORECOMPUTE = ON と同じです。

  • DROP_EXISTING = { ON | OFF }
    名前付きの、既存のクラスタ化、非クラスタ化、または XML インデックスについて、削除および再構築を実行します。既定値は OFF です。

    • ON
      既存のインデックスは削除され、再構築されます。指定するインデックス名は、現在存在するインデックスと同じにする必要がありますが、インデックス定義は変更できます。たとえば、異なる列、並べ替え順、パーティション構成、またはインデックス オプションを指定できます。
    • OFF
      指定するインデックス名が既に存在する場合、エラーが表示されます。

    DROP_EXISTING を使用して、インデックスの種類 (リレーショナルまたは XML) を変更することはできません。また、プライマリ XML インデックスをセカンダリ XML インデックスに、またはその逆に再定義することもできません。

    旧バージョンと互換性のある構文では、WITH DROP_EXISTING は WITH DROP_EXISTING = ON と同じです。

  • ONLINE = { ON | OFF }
    インデックス操作時のクエリとデータ変更で、基になるテーブルと関連するインデックスを使用できるかどうかを指定します。既定値は OFF です。

    ms188783.note(ja-jp,SQL.90).gifメモ :
    オンライン インデックス操作は、SQL Server 2005 Enterprise Edition でのみ実行できます。
    • ON
      長期のテーブル ロックは、インデックス操作の間は保持されません。インデックス操作の主要フェーズの間、ソース テーブルではインテント共有 (IS) ロックのみが保持されます。このことにより、基になるテーブルとインデックスに対するクエリや更新は続行できます。操作の開始時、非常に短い時間ですが、ソース オブジェクトでは共有 (S) ロックが保持されます。操作の終了時、短い時間ですが、非クラクタ化インデックスが作成される場合は、ソース オブジェクト上で共有 (S) ロックの取得が行われます。また、クラスタ化インデックスがオンラインで作成または削除され、クラスタ化または非クラスタ化インデックスが再構築される場合は、SCH-M (スキーマ修正) ロックが取得されます。インデックスがローカル一時テーブルで作成される場合は、ONLINE は ON にできません。
    • OFF
      テーブル ロックは、インデックス操作の間適用されます。クラスタ化インデックスを作成、再構築、または削除するオフライン インデックス操作や、非クラスタ化インデックスを再構築または削除するオフライン インデックス操作では、テーブルのスキーマ修正 (Sch-M) ロックが取得されます。このため、操作中は、すべてのユーザーは基になるテーブルにアクセスできません。非クラスタ化インデックスを作成するオフライン インデックス操作では、テーブルの共有 (S) ロックが取得されます。この場合は、基になるテーブルに対して更新は許可されませんが、SELECT ステートメントなどの読み取り操作は許可されます。

    詳細については、「オンライン インデックス操作の動作原理」を参照してください。ロックの詳細については、「ロック モード」を参照してください。

    インデックスは、グローバル一時テーブル上のインデックスを含めてオンラインで作成できます。ただし次のインデックスは例外です。

    • XML インデックス。
    • ローカル一時テーブル上のインデックス。
    • ビュー上の最初の一意なクラスタ化インデックス。
    • 無効なクラスタ化インデックス。
    • 基になるテーブルに LOB データ型 (image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、および xml 型) が含まれる場合のクラスタ化インデックス。
    • LOB データ型列で定義される非クラスタ化インデックス。
      ms188783.note(ja-jp,SQL.90).gifメモ :
      テーブルに LOB データ型が含まれていても、これらの列がキーまたは非キー列 (付加列) としてインデックス定義で使用されていなければ、一意ではない非クラスタ化インデックスをオンラインで作成できます。

    詳細については、「オンラインでのインデックス操作の実行」を参照してください。

  • ALLOW_ROW_LOCKS = { ON | OFF }
    行ロックを許可するかどうかを指定します。既定値は ON です。

    • ON
      インデックスにアクセスするとき、行ロックが許可されます。データベース エンジンでは行ロックが使用されるタイミングが決定されます。
    • OFF
      行ロックは使用されません。

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    ページ ロックを許可するかどうかを指定します。既定値は ON です。

    • ON
      ページにアクセスするとき、行ロックが許可されます。データベース エンジンではページ ロックが使用されるタイミングが決定されます。
    • OFF
      ページ ロックは使用されません。

  • MAXDOP = max_degree_of_parallelism
    インデックス操作では、max degree of parallelism 構成オプションを無効にします。並列プランの実行で使用されるプロセッサ数を制限するには、MAXDOP を使用します。最大数は 64 プロセッサです。

    max_degree_of_parallelism には次のデータを指定できます。

    • 1
      並列プラン生成を抑制します。
    • >1
      現在のシステム ワークロードに基づいて、並列インデックス操作で使用される最大プロセッサ数を指定の数以下に制限します。
    • 0 (既定値)
      現在のシステム ワークロードに基づいて、実際の数以下のプロセッサを使用します。

    詳細については、「並列インデックス操作の構成」を参照してください。

    ms188783.note(ja-jp,SQL.90).gifメモ :
    並列インデックス操作は、SQL Server 2005 Enterprise Edition でのみ実行できます。

解説

CREATE INDEX ステートメントは、他のクエリと同じように最適化されます。クエリ プロセッサでは I/O 操作を減らすため、テーブル スキャンの代わりに別のインデックスがスキャンされる場合があります。状況によっては、並べ替え操作が行われない場合もあります。SQL Server 2005 Enterprise Edition が搭載されたマルチプロセッサ コンピュータの場合、CREATE INDEX では他のクエリと同様に、インデックス作成に関連するスキャンおよび並べ替え操作を実行するために、より多くのプロセッサを使用することができます。詳細については、「並列インデックス操作の構成」を参照してください。

データベース復旧モデルが一括ログ復旧モデルまたは単純復旧モデルのいずれかに設定されている場合、インデックス作成操作のログへの記録を最小限にできます。詳細については、「インデックス操作の復旧モデルの選択」を参照してください。

一時テーブルにインデックスを作成することもできます。テーブルが削除されるか、セッションが終了すると、インデックスは削除されます。

インデックスでは拡張プロパティがサポートされます。詳細については、「データベース オブジェクトでの拡張プロパティの使用」を参照してください。

クラスタ化インデックス

テーブル (ヒープ) にクラスタ化インデックスを作成したり、既存のクラスタ化インデックスを削除して再作成する場合は、データの並べ替えや、基のテーブルまたは既存のクラスタ化インデックス データの一時的コピーを実行するために、データベース内で追加の作業領域が使用可能になっている必要があります。詳細については、「インデックスに必要なディスク領域の決定」を参照してください。クラスタ化インデックスの詳細については、「クラスタ化インデックスの作成」を参照してください。

一意なインデックス

一意なインデックスが存在する場合、データベース エンジンでは、挿入操作によりデータが追加されるたびに、重複した値がないかがチェックされます。重複キー値が生成される可能性のある挿入操作はロールバックされ、データベース エンジンではエラー メッセージが表示されます。挿入操作で多くの行を変更した場合でも、重複が 1 つでもあれば、ロールバックが行われます。一意なインデックスが存在し、IGNORE_DUP_KEY 句が ON に設定されている場合は、データを入力しようとすると UNIQUE インデックスに違反する行だけが失敗します。一意なインデックスの詳細については、「一意インデックスの作成」を参照してください。

パーティション インデックス

パーティション インデックスは、パーティション テーブルと同様の方法で作成および維持されますが、これらは通常のインデックスのように、個別のデータベース オブジェクトとして扱われます。パーティション分割されていないテーブルにパーティション インデックスを作成したり、パーティション分割されているテーブルに非パーティション インデックスを作成することもできます。

パーティション テーブルにインデックスを作成し、インデックスを配置するファイル グループを指定しない場合、インデックスは基になるテーブルと同じ方法でパーティション分割されます。これは、既定では、インデックスは基になるテーブルと同じファイル グループ上に配置され、また同じパーティション分割列を使用する同じパーティション構成内のパーティション テーブル用に配置されるためです。

一意でないクラスタ化インデックスをパーティション分割するとき、データベース エンジンでは既定により、まだ指定されていないパーティション分割列がクラスタ化インデックス キーのリストに追加されます。

インデックス付きビューは、テーブルのインデックスと同じ方法でパーティション テーブルに作成できます。パーティション インデックスの詳細については、「パーティション テーブルとパーティション インデックス」を参照してください。

インデックス付きビュー

ビューに一意のクラスタ化インデックスを作成すると、そのビューは、クラスタ化インデックスが定義されているテーブルと同じ方法でデータベースに格納されるので、クエリのパフォーマンスが向上します。クエリ オプティマイザではインデックス付きビューを使って、クエリの実行速度を高めることができます。オプティマイザでビューを代用するかどうかを判別するために、ビューがクエリで参照されている必要はありません。

次の手順は、インデックス付きビューの作成に必要な手順であり、ビューの正常な実装には不可欠です。

  1. SET オプションが、ビューで参照されるすべての既存のテーブルに対して正しいことを確認します。
  2. 新しいテーブルとビューを作成する前に、セッション用の SET オプションが正しく設定されていることを確認します。
  3. ビュー定義が決定的であることを確認します。
  4. WITH SCHEMABINDING オプションを使ってビューを作成します。
  5. ビューに一意なクラスタ化インデックスを作成します。

インデックス付きビューに必要な SET オプション

クエリの実行時、異なる SET オプションがアクティブになっている場合、データベース エンジンでは同じ式を評価しても異なる結果が生成されることがあります。たとえば、SET オプションの CONCAT_NULL_YIELDS_NULL を ON に設定した後、式 'abc' + NULL を実行すると NULL 値が返されますが、CONCAT_NULL_YIELDS_NULL を OFF に設定した後、同じ式を実行すると値 'abc' が返されます。

ビューが正しく維持され、一貫性のある結果が返されるようにするには、インデックス付きビューで、いくつかの SET オプションに固定値が必要となります。固定値への設定が必要な SET オプションと、その値 (必要な値の列を参照) を下の表に示します。この設定は次の条件に該当する場合に常に必要となります。

  • インデックス付きビューが作成されている。

  • インデックス付きビューに関係するテーブルで実行される挿入、更新、または削除操作がある。これには一括コピー、レプリケーション、分散クエリなどの操作も含まれます。

  • クエリ オプティマイザで、クエリ プランの生成にインデックス付きビューが使用される。

    SET オプション 必要な値 既定のサーバー値 既定値 OLE DB および ODBC 値 既定値 DB-Library 値

    ANSI_NULLS

    ON

    ON

    ON

    OFF

    ANSI_PADDING

    ON

    ON

    ON

    OFF

    ANSI_WARNINGS*

    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

    *SQL Server 2005 で ANSI_WARNINGS を ON に設定すると、データベース互換性レベルが 90 に設定されている場合、暗黙的に ARITHABORT が ON に設定されます。データベース互換性レベルが 80 以下に設定されている場合は、ARITHABORT オプションを明示的に ON に設定する必要があります。

OLE DB または ODBC サーバー接続を使用している場合、変更する必要があるのは ARITHABORT 設定の値だけです。すべての DB-Library 値は、サーバー レベルで sp_configure を使用するか、アプリケーションから SET コマンドを使用して、正しく設定する必要があります。SET オプションの詳細については、「SQL Server でのオプションの使用」を参照してください。

ms188783.note(ja-jp,SQL.90).gif重要 :
ARITHABORT ユーザー オプションは、サーバー上の任意のデータベースに最初のインデックス付きビューまたは計算列のインデックスを作成したときすぐに、サーバー全体で ON に設定することを強くお勧めします。

決定的な関数

インデックス付きビューの定義は決定的であることが必要です。選択リストのすべての式と、WHERE 句および GROUP BY 句が決定的である場合、ビューは決定的であるといえます。決定的な式では、特定の入力値セットで評価するとき常に同じ結果が返されます。決定的な式には、決定的な関数のみを含めることができます。たとえば、DATEADD 関数は、3 つのパラメータの任意の引数値セットに対して常に同じ結果を返すため、決定的であるといえます。GETDATE は、常に同じ引数で起動されるにもかかわらず、返す値は実行のたびに変化するため、非決定的であるといえます。詳細については、「決定的関数と非決定的関数」を参照してください。

式が決定的でも、float 式が含まれる場合は、正確な結果はプロセッサのアーキテクチャまたはマイクロコードのバージョンによって異なる可能性があります。データの整合性を確保するため、このような式は、インデックス付きビューの非キー列としてのみ含めることができます。float 式を含まない決定的な式は、正確な式です。インデックス ビューのキー列と WHERE または GROUP BY 句には、正確で決定的な式だけを含めることができます。

ビュー列が決定的かどうかを判断するには、COLUMNPROPERTY 関数の IsDeterministic プロパティを使用します。スキーマ バインドを含むビューの決定的な列が正確であるかどうかを判断するには、COLUMNPROPERTY 関数の IsPrecise プロパティを使用します。COLUMNPROPERTY では、TRUE の場合は 1、FALSE の場合は 0、有効でない入力に対しては NULL が返されます。これは、列が決定的でないか、正確でないことを表します。

その他の要件

SET オプションと決定的な関数の要件に加えて、次の要件を満たす必要があります。

  • CREATE INDEX を実行するユーザーが、ビューの所有者であること。

  • ビュー定義に GROUP BY 句を指定した場合、一意なクラスタ化インデックスのキーでは、GROUP BY 句で指定した列のみを参照できること。

  • テーブルの作成時にベース テーブルに正しい SET オプションが設定されていること。設定されていない場合、スキーマ バインドを含むビューでテーブルを参照できません。

  • ビュー定義では、schema**.**tablename という 2 つの部分から構成される名前でテーブルが参照されていること。

  • ユーザー定義関数の場合、WITH SCHEMABINDING オプションを使って作成されていること。

  • ユーザー定義関数が、schema**.**function という 2 つの部分から構成される名前で参照されていること。

  • ビューが、WITH SCHEMABINDING オプションを使って作成されていること。

  • ビューが、他のビューを参照せず、同じデータベース内のベース テーブルのみを参照していること。

  • ビュー定義に次のものが含まれないこと。

    COUNT(*)

    ROWSET 関数

    派生テーブル

    自己結合

    DISTINCT

    STDEV、VARIANCE、AVG

    float*、text、ntext、または image 列

    サブクエリ

    フルテキスト述語 (CONTAIN、FREETEXT)

    NULL 値を許容する式での SUM

    CLR ユーザー定義集計関数

    TOP

    MIN、MAX

    UNION

    *インデックス付きビューには float 列を含めることができますが、このような列はクラスタ化インデックス キーには含めることができません。

GROUP BY が存在する場合、VIEW 定義には COUNT_BIG(*) を含める必要があります。HAVING を含めることはできません。このような GROUP BY 制限は、インデックス付きビュー定義にのみ適用されます。クエリがこの GROUP BY 制限を満たしていない場合でも、実行プランでインデックス付きビューを使用することはできます。

インデックス付きビューはパーティション テーブルで作成でき、インデックス付きビュー自体をパーティション分割できます。パーティション分割の詳細については、前の「パーティション インデックス」を参照してください。

データベース エンジンでインデックス付きビューが使用されないようにするには、クエリに OPTION (EXPAND VIEWS) ヒントを含めます。これによって、オプションの 1 つが正しく設定されていない場合、オプティマイザでビューのインデックスも使用されなくなります。OPTION (EXPAND VIEWS) ヒントの詳細については、「SELECT (Transact-SQL)」を参照してください。

データベースの互換性レベルは 80 未満にできません。インデックス付きビューを含むデータベースは、80 より低い互換性レベルには変更できません。

XML インデックス

詳細については、「xml データ型列のインデックス」を参照してください。

インデックス キー サイズ

インデックス キーの最大サイズは 900 バイトです。900 バイトを超える varchar 列へのインデックスは、インデックス作成時にその列の既存のデータが 900 バイトを超えていなければ作成できます。ただし、後続の挿入や更新操作によって合計サイズが 900 バイトを超えると、その操作は失敗します。詳細については、「インデックス キーの最大サイズ」を参照してください。クラスタ化インデックスのインデックス キーには、ROW_OVERFLOW_DATA アロケーション ユニットにデータを持つ varchar 列を含めることはできません。クラスタ化インデックスを varchar 列で作成し、データが IN_ROW_DATA アロケーション ユニットに存在する場合、後続の挿入や更新操作でデータが行外に押し出されると、その操作は失敗します。アロケーション ユニットの詳細については、「テーブルとインデックスの編成」を参照してください。

SQL Server 2005 では、非クラスタ化インデックスのリーフ レベルに非キー列を含めることができます。インデックス キー サイズを計算するとき、データベース エンジンではこれらの列は考慮されません。詳細については、「付加列インデックス」を参照してください。

計算列

インデックスは計算列に作成できます。SQL Server 2005 では、計算列にプロパティ PERSISTED を設定することができます。この場合、データベース エンジンによってテーブルに計算値が格納され、計算列が依存している他の列が更新されるとその計算値も更新されます。データベース エンジンでは、列にインデックスを作成するとき、およびインデックスがクエリで参照されるときに、これらの保存される値が使用されます。

計算列のインデックスを作成するには、計算列が明確かつ正確である必要があります。ただし、PERSISTED プロパティを使用した場合、インデックス作成が可能となる計算列の種類は、次のようになります。

  • Transact-SQL、CLR 関数、およびユーザーによって決定的とマークされた CLR ユーザー定義型メソッドに基づく計算列
  • データベース エンジンによって決定的と定義されているが、不正確な式に基づく計算列

保存される計算列に対しては、前の「インデックス付きビューに必要な SET オプション」で示すように、次の SET オプションを設定する必要があります。

インデックス作成の条件をすべて満たしている限り、UNIQUE または PRIMARY KEY 制約には計算列を含めることができます。この計算列は、明確かつ正確なものであるか、明確かつ保存されるものである必要があります。決定性の詳細については、「決定的関数と非決定的関数」を参照してください。

image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、および xml 型から派生する計算列は、計算列のデータ型がインデックス キー列または非キー列として許容される限り、キー列または非キー列としてインデックスに登録できます。たとえば、xml 計算列にはプライマリ XML インデックスは作成できません。インデックス サイズが 900 バイトを超える場合、警告メッセージが表示されます。

計算列にインデックスを作成すると、以前は機能していた挿入または更新の操作が失敗することがあります。このような失敗は、計算列の結果が算術エラーになる場合に発生する可能性があります。たとえば、次のテーブルでは、計算列 c は計算エラーになりますが、INSERT ステートメントは正常に実行されます。

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

これに対し、テーブルの作成後に計算列 c にインデックスを作成すると、同じ INSERT ステートメントは失敗します。

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

詳細については、「計算列に対するインデックスの作成」を参照してください。

インデックスの付加列

付加列と呼ばれる非キー列は、非クラスタ化インデックスのリーフ レベルに追加でき、クエリに対応することによりクエリ パフォーマンスを向上できます。この場合、クエリで参照されるすべての列は、キー列または非キー列としてインデックスに含まれます。これにより、クエリ オプティマイザではテーブルまたはクラスタ化インデックス データにアクセスすることなく、インデックス スキャンによって必要な情報をすべて特定できます。詳細については、「付加列インデックス」を参照してください。

インデックス オプションの指定

SQL Server 2005 では新しいインデックス オプションが導入され、オプションの指定方法も変更になりました。旧バージョンと互換性のある構文では、WITH option_name は WITH ( <option_name> = ON ) と同じです。インデックス オプションを設定する場合は、次の規則が適用されます。

  • 新しいインデックス オプションは、WITH (option_name= ON | OFF**)** を使用してのみ指定できる。
  • 同じステートメントで、旧バージョンとの互換性がある構文と新しい構文の両方を使ってオプションを指定することはできない。たとえば、WITH (DROP_EXISTING, ONLINE = ON**)** を指定すると、ステートメントは失敗します。
  • XML インデックスを作成するとき、オプションは WITH (option_name= ON | OFF**)** を使用して指定する必要がある。

DROP_EXISTING 句

DROP_EXISTING 句を使用して、インデックスの再構築、列の追加または削除、オプションの変更、列の並べ替え順の変更、パーティション構成またはファイル グループの変更を行えます。

インデックスに PRIMARY KEY または UNIQUE 制約が設定されており、どの方法でもインデックス定義を変更できない場合は、既存の制約を保持したままインデックスが削除され再作成されます。ただし、インデックス定義が変更されると、ステートメントは失敗します。PRIMARY KEY または UNIQUE 制約の定義を変更するには、制約を削除し、新しい定義で制約を追加します。

DROP_EXISTING を使用すると、非クラスタ化インデックスが定義されているテーブル上で、同じまたは異なるキー セットのクラスタ化インデックスを再作成するときのパフォーマンスを向上できます。DROP_EXISTING では、古いクラスタ化インデックスに DROP INDEX ステートメントを実行した後、新しいクラスタ化インデックスに CREATE INDEX ステートメントを実行するという操作を一度に実行できます。非クラスタ化インデックスは一度だけ再構築され、その後はインデックス定義が変更された場合のみ再構築されます。インデックス定義に元のインデックスと同じインデックス名、キーおよびパーティション列、一意性属性、および並べ替え順がある場合、DROP_EXISTING 句で非クラスタ化インデックスは再構築されません。

非クラスタ化インデックスが再構築されるかどうかに関係なく、非クラスタ化インデックスは元のファイル グループまたはパーティション構成に常に属したままになり、元のパーティション関数を使用します。クラスタ化インデックスが異なるファイル グループまたはパーティション構成に再構築される場合、クラスタ化インデックスの新しい位置に非クラスタ化インデックスは移動しません。したがって、以前に非クラスタ化インデックスがクラスタ化インデックスに対応した位置にあっても、再構築後は別の位置になる可能性があります。パーティション インデックス配置の詳細については、「パーティション インデックスの専用ガイドライン」を参照してください。

インデックス ステートメントで非クラスタ化インデックスが指定されず、かつ ONLINE オプションが OFF に設定されない限り、同じインデックス キー列が同じ順序 (昇順または降順も同じ) で使用される場合、DROP_EXISTING 句では再度データの並べ替えは行われません。クラスタ化インデックスが無効な場合、CREATE INDEX WITH DROP_EXISTING 操作は ONLINE が OFF に設定された状態で実行する必要があります。非クラスタ化インデックスが無効で、無効なクラスタ化インデックスと関連がない場合、CREATE INDEX WITH DROP_EXISTING 操作は、ONLINE が OFF または ON に設定された状態で実行できます。

128 以上のエクステントがあるインデックスを削除または再構築すると、データベース エンジンでは、トランザクションがコミットされるまで実際のページの割り当て解除とそれに関連するロックが延期されます。詳細については、「ラージ オブジェクトの削除と再構築」を参照してください。

ONLINE オプション

インデックス操作をオンラインで実行する場合は、次のガイドラインが適用されます。

  • オンライン インデックス操作の実行中、基になるテーブルは変更、切り捨て、削除できない。
  • インデックス操作中は、追加の一時ディスク領域が必要。詳細については、「インデックスに必要なディスク領域の決定」を参照してください。
  • オンライン操作は、パーティション インデックスや保存される計算列を含むインデックス、または付加列で実行できる。

詳細については、「オンラインでのインデックス操作の実行」を参照してください。

行およびページ ロック オプション

ALLOW_ROW_LOCKS = ON かつ ALLOW_PAGE_LOCK = ON の場合は、インデックスにアクセスするときに、行、ページ、およびテーブル レベルのロックが許可されます。データベース エンジンでは適切なロックが選択されるほか、行またはページ ロックからテーブル ロックへ、ロックのレベルを引き上げることができます。詳細については、「ロックのエスカレーション (データベース エンジン)」を参照してください。

ALLOW_ROW_LOCKS = OFF かつ ALLOW_PAGE_LOCK = OFF の場合は、インデックスにアクセスするときに、テーブル レベルのロックのみが許可されます。

インデックスに対するロックの粒度の構成に関する詳細については、「インデックスのロックのカスタマイズ」を参照してください。

インデックス情報の表示

インデックスに関する情報を返すには、カタログ ビュー、システム関数、およびシステム ストアド プロシージャを使用できます。詳細については、「インデックス情報の表示」を参照してください。

権限

テーブルまたはビューに対する ALTER 権限が必要です。実行するには、固定サーバー ロール sysadmin または、固定データベース ロール db_ddladmin および db_owner のメンバである必要があります。

例

A. 単純な非クラスタ化インデックスを作成する

次の例では、Purchasing.ProductVendor テーブルの VendorID 列に非クラスタ化インデックスを作成します。

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (VendorID); 
GO

B. 単純な非クラスタ化複合インデックスを作成する

次の例では、Sales.SalesPerson テーブルの SalesQuota 列および SalesYTD 列に非クラスタ化複合インデックスを作成します。

USE AdventureWorks
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

C. 一意な非クラスタ化インデックスを作成する

次の例では、Production.UnitMeasure テーブルの Name 列に一意な非クラスタ化インデックスを作成します。このインデックスにより、Name 列に挿入されるデータは一意であることが必要になります。

USE AdventureWorks;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

次のクエリでは、既存の行と同じ値の行の挿入を試行し、一意性の制約をテストします。

--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
    VALUES ('OC', 'Ounces', GetDate());

結果のエラー メッセージは次のようになります。

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

D. IGNORE_DUP_KEY オプションを使用する

次の例では、最初に IGNORE_DUP_KEY オプションを ON に設定し、次にこのオプションを OFF に設定して、複数の行を一時テーブルに挿入したときのオプションの影響を検証します。2 番目の複数行の INSERT ステートメントを実行するときには、#Test テーブルに、重複する値となる 1 行を意図的に挿入します。テーブル内の行数としては、挿入された行数が返されます。

USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

次は 2 番目の INSERT ステートメントの結果です。

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows 
-------------- 
38

一意性の制約に違反していない Production.UnitMeasure テーブルからの行は、正常に挿入されています。ここでは警告が発行され、重複する行が無視されましたが、トランザクション全体はロールバックされていません。

次に、IGNORE_DUP_KEY を OFF に設定して同じステートメントを実行します。

USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

次は 2 番目の INSERT ステートメントの結果です。

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows 
-------------- 
1

ここでは、Production.UnitMeasure テーブルで UNIQUE インデックス制約に違反した行は 1 行だけでしたが、このテーブルから行は挿入されませんでした。

E. DROP_EXISTING を使ってインデックスを削除し再作成する

次の例では、DROP_EXISTING オプションを使って、Production.WorkOrder テーブルの ProductID 列にある既存のインデックスを削除して再作成します。ここではオプション FILLFACTOR および PAD_INDEX も設定されています。

USE AdventureWorks;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

F. ビューにインデックスを作成する

次の例では、ビューとそのビューのインデックスを作成します。ここでは、インデックス付きビューを使用する 2 つのクエリを実行します。

USE AdventureWorks;
GO
--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;
GO
--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
--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 ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can 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 DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

G. インデックスを非キー列 (付加列) を使用して作成する

次の例では、1 つのキー列 (PostalCode) と 4 つの非キー列 (AddressLine1、AddressLine2、City、StateProvinceID) を使って非クラクタ化インデックスを作成します。次に、インデックスが対応するクエリを実行します。クエリ オプティマイザによって選択されるインデックスを SQL Server Management Studio の [クエリ] メニューに表示するには、クエリを実行する前に [実際の実行プランを含める] を選択します。

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
    ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

H. プライマリ XML インデックスを作成する

次の例では、Production.ProductModel テーブルの CatalogDescription 列にプライマリ XML インデックスを作成します。

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription 
        ON Production.ProductModel;
GO
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);
GO

I. セカンダリ XML インデックスを作成する

次の例では、Production.ProductModel テーブルの CatalogDescription 列にセカンダリ XML インデックスを作成します。

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;
GO
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path 
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

J. パーティション インデックスを作成する

次の例では、既存のパーティション構成 TransactionsPS1 に非クラスタ化パーティション インデックスを作成します。この例では、パーティション インデックスのサンプルがインストールされていることを前提としています。インストールについては、「Readme_PartitioningScript」を参照してください。

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_TransactionHistory_ReferenceOrderID')
    DROP INDEX IX_TransactionHistory_ReferenceOrderID
        ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
ON Production.TransactionHistory (ReferenceOrderID)
ON TransactionsPS1 (TransactionDate);
GO

参照

関連項目

ALTER INDEX (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
CREATE TABLE (Transact-SQL)
データ型 (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP INDEX (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.xml_indexes (Transact-SQL)
EVENTDATA (Transact-SQL)

その他の技術情報

インデックスに必要なディスク領域の決定
インデックスの設計の全般的なガイドライン
xml データ型列のインデックス
テーブルとインデックスのアーキテクチャ

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース 履歴

2006 年 4 月 14 日
変更内容
  • オンラインで作成できないインデックスの一覧から一意の非クラスタ化インデックスを削除。この変更は、SQL Server 2005 Service Pack 1 以上に適用されます。
  • SET オプションの表に、ANSI_WARNINGS を ON に設定することによる ARITHABORT の設定への影響に関する脚注を追加。

2005 年 12 月 5 日
新しい内容
  • オンラインで作成できないインデックスの一覧に一意の非クラスタ化インデックスを追加。