// Net planet-es V2

SQL Server 2005 のインデックスとプライマリキーの設定と有効化、無効化方法を掲載します。

T-SQLの動作確認はSQL Server 2005 Developer Edition で行っています。

1. インデックスの定義

1.1 プライマリキー

インデックスと同じではありませんが、テーブルの定義にプライマリキー制約を追加する場合は、次のようにALTER TABLE を発行します。クラスタ化プライマリキーとする場合は、PRIMARY KEY に続いてCLUSTERED を指定します。詳しくはBooks Onlineを参照してください。また、カラムがNULLを許容する場合はプライマリキー制約を作成できません。

ALTER TABLE dbo.TestTable
ADD CONSTRAINT PK_TestTable
PRIMARY KEY (ColumnKey1)

テーブルdbo.SomeTableに制約名PK_TableKeyで、キー列をKeyColumnとしてプライマリキー制約を付与しています。

1.2 インデックスの定義

dbo.TestTable の列IndexCol1,IndexCol2に非クラスタインデックスを設定するサンプルを次に示します。インデックスで指定した列がUNIQUEとなる制約とする場合は、CREATE UNIQUE NONCLUSTEREDとします。インデックスのソート方向を指定することもできます。エンタープライズエディションの場合は、WITH(ONLINE = ON) を指定することで、インデックス構築中でもクエリを実行できるようになります。詳細はBooks Online 参照。

CREATE NONCLUSTERED INDEX IDX_TestTable1
ON dbo.TestTable(IndexCol1, IndexCol2)

クラスタ化インデックスとする場合は、NONCLUSTEREDではなく、CLUSTEREDを指定します。使用可能な構文の詳細はBooks Online を参照願います。

1.3 インデックスを無効化する

無効化するには次の構文を使用します。サンプルではテーブルTestTableのインデックス名IDX_TestTable1を無効化しています。

ALTER INDEX IDX_TestTable1 ON
dbo.TestTable DISABLE

無効化されているすべてのインデックスを再有効にしインデックスを構築しなおす場合はALTER INDEX REBUILD を使用します。特定のインデックスのみ再構築する場合はALLの部分にインデックスを指定します。関連するビューなどのインデックスも再構築されます。

ALTER INDEX ALL ON
dbo.TestTable REBUILD

そのほか無効化されたインデックスを有効化するにはCREATE INDEX WITH(DROP_EXISTING = ON)文を使用します。DROP EXISTING はインデックスを削除し再構築を行う処理を1つの文で行っています。次がサンプルです。IDX_TestTable1を削除し同じインデックス名で再構築しています。

CREATE NONCLUSTERED INDEX IDX_TestTable1 ON
dbo.TestTable(ColumnKey1)
WITH(DROP_EXISTING = ON)

1.4 インデックスを削除する

インデックスを削除する場合はDROP INDEX文を使用します。サンプルでは、TestTableテーブルのインデックスIDX_TestTable1を削除しています。

DROP INDEX dbo.TestTable.IDX_TestTable1

1.5 インデックスのメタデータを取得する

sys.カタログビューを使用すると、インデックスの情報(有効、無効状態、クラスタ化インデックスかなど)を取得できます。以下の例ではテーブルTestTableのインデックス情報を取得しています。

select *
 from sys.indexes
where object_id = OBJECT_ID('TestTable')

1.6 INCLUDEカラムを追加する

SQL Server 2005 から非クラスタ化インデックスにINCLUDEオプションを使用してクラスタ化インデックスを介して列を取得せずにインデックスに列を含めるようにすることができます。これにより、INCLUDEで指定した列にカラムが含まれていれば(インデックスのカラムとINCLUDEで指定された列で選択列がカバーされている)クラスタ化されたインデックスにアクセスして実データ列を読み込む必要がなくなります。サンプルでは、INCLUDEカラムにOtherCol1を指定しています。

CREATE NONCLUSTERED INDEX IDX_TestTable1 ON
dbo.TestTable(ColumnKey1)
INCLUDE (OtherCol1)

1.7 FILEGROUP を指定してインデックスを作成する

インデックスを作成する場合、製品環境ではインデックスのファイルとテーブルのファイルは別になるようにすると思います。次のサンプルではTEST_FGというファイルグループにインデックスを構築するようにします。指定しない場合はテーブルと同じファイルグループにインデックスが作成されます。

CREATE NONCLUSTERED INDEX IDX_TestTable
dbo.TestTable(ColumnKey1)
ON [TEST_FG]

1.8 インデックスの統計情報を更新する

DBの統計情報を更新するには、DBCCを使う方法もありますが、全体を更新するには sp_updatestats ストアドプロシージャを実行します。

USE DB名:
GO:
 
EXEC sp_updatestats

2. おわりに

以上で説明は終わりです。誤り、指摘点などがあればご連絡ください。

インデックスの作成オプションはインデックス作成の中間状態をtempdbに作成するようにしたり(SORT_IN_TEMPDB)、インデックス構築に使用するCPUの数を制御(MAXDOP)することができるので、Books online を調べてみるとよいかもしれません。