データ移行前後のINDEX調査と追加SQL

データ移行プロジェクトで見落とされがちなのが、既存テーブルのINDEX構成です。スキーマだけ移行してINDEXを再現していない、あるいは環境差分で抜け漏れがあると、移行後に性能問題が顕在化します。本記事では「既存環境のINDEXを調査するSQL」と「その結果をもとにINDEXを追加するSQL生成の考え方」を、実務メモレベルで整理します。移行前の棚卸しから、移行先への安全な反映までを端的にまとめます。

既存テーブルのINDEXを網羅的に調査する
移行時に見るべき実務ポイント
調査結果からINDEX追加SQLを生成する
実務で使える「INDEX生成SQL」作成クエリ例
データ移行時のINDEX作成タイミング設計
まとめ
補足）CLUSTERED と NONCLUSTERED の違い

既存テーブルのINDEXを網羅的に調査する

データ移行では、まず「何が定義されているか」を正確に取得することが重要です。INDEXの定義はテーブル作成SQLだけでは把握できません。主キー・一意制約・非クラスタ化INDEX・フィルター条件・INCLUDE列まで含めて調査することで、はじめて完全な再現が可能になります。このセクションでは、実務でそのまま使える調査SQLを整理します。

INDEX定義の基本情報を取得するSQL

まずはINDEXの全体像を把握します。種類や一意制約の有無、フィルターの存在を確認することが第一歩です。

INDEX基本情報取得SQLの例

SELECT
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_primary_key,
    i.is_unique,
    i.is_unique_constraint,
    i.fill_factor,
    i.has_filter,
    i.filter_definition
FROM sys.indexes i
INNER JOIN sys.tables t
    ON i.object_id = t.object_id
WHERE t.name = '対象テーブル名'
ORDER BY i.index_id;

✅ 確認ポイント

type_desc が CLUSTERED か NONCLUSTERED か
is_primary_key = 1 かどうか
has_filter = 1 の場合は filter_definition を必ず保存

実務ではこの結果をExcelやCSVに出力し、移行先と並べて比較するだけでも大きな事故防止になります。

⚠️ よくある誤解として、「主キー＝クラスタ化INDEX」と思い込むケースがあります。しかし主キーが非クラスタ化で、別のクラスタ化INDEXが存在する場合もあるため注意が必要です。

INDEX列（キー列・INCLUDE列）を取得するSQL

INDEXは「どの列で構成されているか」が本質です。列順序も性能に直結します。

INDEX列取得SQLの例

SELECT
    t.name AS TableName,
    i.name AS IndexName,
    c.name AS ColumnName,
    ic.key_ordinal,
    ic.is_included_column
FROM sys.indexes i
INNER JOIN sys.index_columns ic
    ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id
INNER JOIN sys.columns c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
INNER JOIN sys.tables t
    ON i.object_id = t.object_id
WHERE t.name = '対象テーブル名'
ORDER BY i.index_id, ic.key_ordinal;

key_ordinal = 0 かつ is_included_column = 1 がINCLUDE列です。

👉 実務上の重要ポイント

key_ordinal の順序は必ず維持する
INCLUDE列は検索条件には使われないが、カバリングINDEXとして重要
INCLUDEの抜け漏れは実行計画悪化の原因になる

特に移行後に「なぜか遅い」という問い合わせの多くは、INCLUDE列の未再現が原因であるケースが少なくありません。

移行時に見るべき実務ポイント

単に「INDEXがあるか」だけでなく、設計意図まで読み取ることが重要です。INDEXはパフォーマンス設計そのものだからです。ここでは移行時に必ずチェックすべき観点を整理します。

クラスタ化INDEXの有無（Heapになっていないか）
主キーとクラスタ化INDEXの一致／不一致
フィルター付きINDEXの存在
同一列構成の重複INDEX
環境差分（本番と検証で異なる）

データ移行特有の注意点

データ移行では通常運用と異なるリスクが発生します。

✅ IDENTITY再生成の影響

移行先でIDENTITYを再作成する場合、クラスタ化キーの値分布が変わる可能性があります。これにより断片化率やページ分割頻度が変わることがあります。

✅ 照合順序や文字コード差分

照合順序が異なる場合、INDEXの再作成が必要になるケースがあります。特にLIKE検索を多用するシステムでは影響が顕著です。

✅ 大量データ投入との関係

事前にINDEXを作成するとINSERT性能が大幅に低下します。逆に後作成するとCREATE時に時間がかかります。設計判断が必要です。

初心者がつまずきやすいのは、「移行＝単なるコピー」と考えてしまう点です。実際は物理設計の再構築作業でもあります。

調査結果からINDEX追加SQLを生成する

移行先にINDEXが存在しない場合、調査結果をもとにCREATE文を生成します。ここでは基本形を整理します。

非クラスタ化INDEXの基本形

CREATE INDEX基本例

CREATE NONCLUSTERED INDEX IX_テーブル名_列名
ON dbo.テーブル名 (列1 ASC, 列2 DESC)
INCLUDE (列3, 列4)
WITH (FILLFACTOR = 90);

👉 実務で意識すべき点

FILLFACTORは元環境に合わせる
ASC/DESCの違いも再現する
ファイルグループ指定がある場合は忘れない

フィルター付きINDEXの例

フィルター付きINDEX例

CREATE NONCLUSTERED INDEX IX_テーブル名_Status
ON dbo.テーブル名 (Status)
WHERE Status = 1;

has_filter = 1 の場合、filter_definition をそのまま反映します。

⚠️ フィルター条件を手動再入力すると誤記が起きやすいため、必ず調査SQLの結果をコピーするのが安全です。

実務で使える「INDEX生成SQL」作成クエリ例

手作業はミスの温床です。そのため既存環境でCREATE文を出力させるアプローチが有効です。

CREATE文生成SQL例

SELECT
    'CREATE ' +
    CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
    CASE WHEN i.type = 1 THEN 'CLUSTERED ' WHEN i.type = 2 THEN 'NONCLUSTERED ' END +
    'INDEX [' + i.name + '] ON [' + s.name + '].[' + t.name + '] (' +
    STRING_AGG(
        '[' + c.name + ']' + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END,
        ', '
    ) WITHIN GROUP (ORDER BY ic.key_ordinal) +
    ');' AS CreateIndexSql
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.index_columns ic
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c
    ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE t.name = '対象テーブル名'
  AND i.index_id > 0                 -- HEAP除外
  AND i.type IN (1,2)                -- CLUSTERED/NONCLUSTEREDのみ
  AND ic.is_included_column = 0      -- キー列だけ
  AND i.is_primary_key = 0
GROUP BY i.name, i.type, i.is_unique, s.name, t.name;

実務ではさらに

INCLUDE列出力ロジック追加
filter_definition出力追加
DROP文も同時生成

といった拡張を行います。

データ移行時のINDEX作成タイミング設計

INDEXは「いつ作るか」で移行時間が大きく変わります。ここは設計判断が求められるポイントです。

大量データ投入前に作成 → INSERTが遅い

投入後にまとめて作成 → CREATE時に時間がかかる

ログ肥大化対策として、バルクロード後にINDEXを作成する方式が一般的です。

推奨パターン（大量移行時）

実務で時間短縮しやすい順序は以下です。

テーブルのみ作成
データ一括投入（必要に応じてバルクログ）
クラスタ化INDEX作成
非クラスタ化INDEX作成

この順序にすることでページ分割を抑え、総移行時間を短縮できます。特に数千万件以上のデータでは効果が顕著です。

まとめ

データ移行におけるINDEX調査は、「sys.indexes／sys.index_columnsから正確に定義を取得すること」が出発点です。そのうえで、クラスタ化構成・フィルター・INCLUDE列まで含めて整理し、CREATE INDEX文を生成して移行先へ反映します。

実務では以下の流れをテンプレート化すると再利用性が高まります。

移行前にINDEX棚卸しSQLを実行
CREATE文をスクリプト化
移行後に差分確認
実行計画で最終確認

まずは対象テーブル1つでINDEX一覧を出力し、「移行先と完全一致しているか」を比較するところから始めてみてください。

INDEXは単なる補助オブジェクトではなく、性能設計そのものです。移行成功の可否を左右する重要要素として、必ず棚卸しを実施しましょう。

補足）CLUSTERED と NONCLUSTERED の違い

「CLUSTERED と NONCLUSTERED の違いがよく分からない」という声は非常に多いです。

ここでは、仕組み＋いつ決まるのかまで含めて、初心者向けに整理します。

まず、いちばんシンプルな理解からいきましょう。

CLUSTERED = データそのものの並び順を決めるINDEX
NONCLUSTERED = データを探すための目次INDEX

この違いが本質です。

イメージで理解する

紙の名簿を想像してください。

✅ CLUSTERED のイメージ

名簿が「社員番号順」に並んでいる状態です。

並び順そのものが決まっています。この並び順は1つしか持てません。

✅ NONCLUSTERED のイメージ

名簿の後ろに「部署別索引」「氏名索引」が付いている状態です。

名簿の並びは変わらず、探し方だけ増えます。

索引は何個でも作れます。

つまり、

CLUSTEREDは“本体の並び順”
NONCLUSTEREDは“検索用の追加索引”

です。

いつ決まるのか？

ここが初心者の方が見落としやすいポイントです。

CLUSTEREDかどうかは、

テーブル作成時、またはINDEX作成時に決まります。

あとから自動で変わるものではありません。

たとえば次のように書いた場合です。

CREATE TABLE Users (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100)
);

多くの環境では、

PRIMARY KEY
かつ CLUSTERED

として作られます。

そのため「主キー＝CLUSTERED」と思われがちです。

しかし実際は、次のようにも書けます。

CREATE TABLE Users (
    Id INT PRIMARY KEY NONCLUSTERED,
    CreatedAt DATETIME
);

CREATE CLUSTERED INDEX IX_Users_CreatedAt
ON Users (CreatedAt);

この場合は、

主キー → NONCLUSTERED
CreatedAt → CLUSTERED

になります。

つまり、

主キー = 一意制約
CLUSTERED = 並び順の指定

であり、役割が違います。

根本的な違いは何か？

いちばん大事なのはここです。

✅ CLUSTERED

データがそのキー順に物理的に並ぶ
1つしか作れない
変更するとテーブル全体が再構築される
範囲検索に強い

✅ NONCLUSTERED

データの並びは変わらない
複数作れる
検索条件ごとに追加できる
作りすぎると更新が遅くなる

特に重要なのは、

CLUSTEREDを変更する = テーブルを並び替えること

という点です。

これは大規模データでは非常に重い処理になります。

データ移行でなぜ重要か

移行時によくある問題は、

元環境では主キーがCLUSTEREDだった
移行先では主キーがNONCLUSTEREDになっていた
しかもCLUSTEREDが存在せずHeapになっていた

というケースです。

これは「作成時のSQLが違った」ことが原因です。

CLUSTEREDは設計時に決まるため、

移行先で同じ定義を再現しなければ性能が変わります。

まとめ

初心者向けに整理すると、次の理解で十分です。

CLUSTERED = テーブルの並び順そのもの（1つだけ）
NONCLUSTERED = 検索用の目次（複数可）
主キーとCLUSTEREDは“たまたま一致していることが多い”だけ
どちらになるかは、作成時のSQLで決まる

まずは

「CLUSTEREDは並び順を決めるもの」

と覚えてください。

ここが理解できると、データ移行やINDEX設計の考え方が一気に整理されます。