マイクロソフトの植田です。
今回はテーブル・スキーマの変更に関する話題をご紹介したいと思います。
http://blogs.msdn.com/sqlcat/archive/2006/03/01/541550.aspx
注：下記内容に関する詳細の確認をご希望される場合は上記のブログを参照いただけますようお願いします。
本ドキュメントは以下の方を対象としております。
l 開発者、テストエンジニア、データベース・アドミニストレータ
l データベース、および、Microsoft SQL Serverについて基本的な知識をお持ちの方
スキーマ変更の制御（その１）
質問：SQL Server 2005においてスキーマ変更を行う際のベスト・プラクティスはなんでしょうか。最も早く、簡単な方法は何でしょうか？
回答：ビジネス要件は時間とともに変化していきます、その変化に合わせてスキーマ変更の要求が発生することは珍しくありません。SQL Server 2005はスキーマ変更（例えば、列や制約の追加/削除、列のデータ型やプロパティの変更）をサポートしていますが、本ブログでは大きなテーブルを操作する際に考慮しなければならないパフォーマンスと並行性の問題にフォーカスします。そしてパフォーマンスと並行性を最大限に活用するベスト・プラクティスについては次の機会（その２:http://blogs.msdn.com/sqljapan/archive/2007/08/20/4471435.aspx）で取り扱います。
では、現在ではかなり一般的になってきた、10億行規模のテーブルを想定して話を進めます。可用性の観点から、スキーマ変更はできるだけ早く、出来るだけ簡単な方法で行う必要があります。
いくつかのスキーマ変更ではバックグラウンドでの読み出し、書き込みが発生します。これらの処理はスキーマ変更の時間を引き延ばし、かつ、ロックを確保することによって（読み込み時には共有ロック、書き込み時には排他ロック）、並行性を低下させる恐れがあります。例えば、チェック制約を追加する際は、制約を施行するためにテーブル・スキャンを行います。NOT NULL制約、列のデータ型やサイズの変更を含むスキーマ変更はバックグラウンドでのアップデートを実行します。これらの暗黙的な読み出しや書き込みはプロファイラーによるステートメント・レベルのトラッキング、および、「SET STATISTICS IO ON」を使うことによって調べることができます。
これらの処理をプロファイラーで追跡するためには、以下の項目を選択してください。
1. TSQL イベント：SQL:StmtStarting、SQL:StmtCompleterd
2. ストアドプロシージャイベント：SP:StmtStarting、SP:StmtCompleted
列の追加 ― alter table MyTable add NewColumn……
＃
列のプロパティ
列につけられている
制約
プロファイラーによるモニタ (StmtStarting &StmtCompleted)
パフォーマンス、および、同時実行に対する影響
1
NULL
N/A
alter table MyTable
add MySSN numeric(9,0) NULL
最速。
最高のパフォーマンス、および、並行性。
メタデータの変更のみ。
2
DEFAULT
add MyDate2 datetime NULL
default getdate()
3
CHECK
add MyPct3 numeric(4,1) NULL constraint MyChk3
check (MyPct3 between 0 and 100)
統計を採取し、その後チェック制約施行のため、テーブル全体を読み込み。
NOT NULLプロパティ設定時よりも高速、しかしチェック制約による読み込みが発生。
大きなテーブルでは、チェック制約のための読み込み処理が書き込みをブロックする可能性がある。
4
CHECK, DEFAULT
add MyInt4 int NULL default 0
constraint myChk4
check (MyInt4 between 0 and 100)
統計を採取し、その後チェック制約施行のため、テーブル全体の読み込みを行う。
5
WITH VALUES
add MyInt5 int NULL default 0
with values
テーブル全体をデフォルトの値で更新
UPDATE [Northwind].[dbo].[MyTable]
SET [MyInt5] = DEFAULT
NULL & Default (#2)の場合よりも低速。
「WITH VALUE」句によって存在するテーブルの全ての行が更新されるため。
大きなテーブルでは、このバッチ更新処理は並行性の問題を引き起こす恐れがある。
6
add MyInt6 int NULL default 0
constraint myChk6
check (MyInt6 between 0 and 100)
テーブル全体をデフォルトの値で更新し、統計を採取、その後チェック制約施行のため、テーブル全体の読み込みを行う。
SET [MyInt6] = DEFAULT
最も遅い。「WITH VALUE」句によって存在するテーブルの全ての行が更新され、かつ、チェック制約のための読み込みが行われるため。
デフォルトの値によるバッチ更新処理、および、チェック制約のための読み込み処理が発生するため、大きなテーブルでは顕著な並行性の問題が発生する可能性がある。
7
NOT NULL
add MyDate7 datetime NOT NULL
constraint myDft7
NOT NULL制約によりテーブル全体のデフォルト値による更新処理が要求される。
SET [MyDate7] = DEFAULT
NULL & Default (#2)の場合よりも低速。「NOT NULL」制約が、デフォルトの値による更新を要求するため。
大きなテーブルでは、このバッチ更新処理が排他ロックを握るため、並行性の問題を引き起こす可能性がある。
8
add MyInt8 int
constraint myDft8
default 0
constraint myChk8
check (MyInt8 between 0 and 100)
最も遅い。デフォルトの値による更新処理、および、チェック制約のための読み込み処理のため。このデフォルトの値によるバッチ更新処理、および、チェック制約のための読み込み処理により、大きなテーブルでは顕著な並行性の問題が発生する恐れがある。
大きなテーブルに新しい列を追加する最も速い方法は（DEFAULT制約のありなしに因らず）NULLプロパティを使ったやり方です（上記の例の#1、#2参照）。#2の例ではDEFAULT制約がありますが、MyDate列の値は全ての行でNULLになります。
大きなテーブルにチェック制約を含めると、チェック制約を施行するためにテーブル全体の読み込み処理が発生します（上記の例の#3、#4、#6、#8参照）。#3、#4の例ではそれぞれ新規に追加したMyPct3列とMyInt4列にNULLの値が含まれますが、NULL値はチェック制約で不正とならないことに注意してください。NULLはただ単にその値が分からないということを意味します。しかし、もしその値を分かっているものであればチェック制約は強制的にその値をチェックの条件に適応させます。
#5から#8の例ではバッチ更新処理が発生します。もしNULLおよびDEFAULTプロパティに加えてWITH VALUES句が指定されていれば（#5、#6参照）、全ての行がデフォルトの値で更新されます。NOT NULLとDEFAULTの組み合わせによりバッチ更新処理が発生します。バッチ更新処理は排他ロックを保持し、並行性を低下させるので、これらのケースは理想的なパフォーマンス、および、可用性の目標には到達できません。
書き込み処理が発生するケースにチェック制約を加えた場合、#6：NULLプロパティのケース、#8：NOT NULLプロパティのケースともにチェック制約のための読み込み処理が発生し、パフォーマンス、および、可用性が最も低くなります。
列のデータ型、デフォルト制約、チェック制約、NULLプロパティの変更
alter table alter columnステートメントを使って列のデータ型やデータ長（numeric型の場合はスケール、および、精度が含まれる）を変更することができます。これらのケースでは変更に付随して行われるバッチ更新処理によって、パフォーマンス、および、並行性が影響を受けます。もし、NULLプロパティがNOT NULLに変更されると、バッチ更新処理が発生します。例外はNOT NULLプロパティがNULLに変更される時です。これはメタデータのみの高速な処理で行われます。
列プロパティの変更、データ型、および、データ長、列制約の追加 － alter table MyTable alter column
変更
プロファイラーによる観察
プロパティ 変更
NOT NULLからNULL
alter column MyDate7 datetime NULL
高速
メタデータのみ変更
NULLからNOT NULL
alter column MyDate7 datetime NOT NULL
バッチ更新処理実行
UPDATE [Northwind].[dbo].[MyTable] SET [MyDate7] = [MyDate7]
大きなテーブルでは、変更に引き続き起こるバッチ更新処理により並行性の問題が発生する恐れがある。
デフォルトプロパティ追加
ALTER TABLE MyTable
ADD CONSTRAINT MyDft
DEFAULT 0 FOR MySSN
デフォルトプロパティ削除
DROP CONSTRAINT MyDft
チェック制約追加
ADD CONSTRAINT MyDtChk
Check (MyDate2 <= getdate())
大きなテーブルでは、変更に引き続き起こるチェック制約のための読み込み処理により並行性の問題が発生する恐れがある。
チェック制約削除
DROP CONSTRAINT MyDtChk
データ長の 拡張
NUMERIC(9)から(11)
ALTER COLUMN MySSN numeric(11,0)
更新処理実行
UPDATE [Northwind].[dbo].[MyTable] SET [MySSN] = [MySSN]
データ型の 変更
NUMERICからINT
ALTER COLUMN MySSN int
INTからVarchar(9)
ALTER COLUMN MySSN varchar(9)
Varchar(9)からVarchar(15)
ALTER COLUMN MySSN varchar(15)
データ長の 縮小
Varchar(15)からVarchar(10)
Varchar(15)からChar(15)
ALTER COLUMN MySSN char(15)
Char(15)からchar(20)
ALTER COLUMN MySSN char(20)
列の削除
列の削除はスキーマのみの操作になります。この場合は、削除される列からオブジェクトを再利用スペースに移動させない限り、並行性の問題は発生しません。
ユニーク、および、プライマリーキー制約
ユニーク、および、プライマリーキー制約の追加は読み込み処理、ソート処理、書き込み処理を含みます。既定では、ユニーク制約は非クラスタ化インデックスを付加し、プライマリーキー制約はクラスタ化インデックスを付加します。すでにユニーク制約（非クラスタ化イン��ックス）を持っている大きなテーブルに対してプライマリーキー制約（そしてデフォルトのクラスタ化インデックス）を追加する場合、パフォーマンス、および、並行性の問題が発生することは明白です。これはプライマリーキー（クラスタ化インデックス）を追加することによって非クラスタ化インデックスの再構築が行われ、RID（列ID）がプライマリーキーに置き換えられるためです。
Alter table, create/alter indexはユニーク、および、プライマリーキー制約/インデックスを制御するためのONLINEオプションを持っています。可能であれば、最高のパフォーマンス、および、最も高い並行性を実現するために、インデックス操作の際はONLINEオプションを使うべきです。もしくは、メンテナンスの時間を別途設けてこれらの操作を行うのが賢明でしょう。
結論、および、ベスト・プラクティス
SQL Server 2005の動作を理解することにより、効果的にスキーマの変更を制御することができます。このナレッジによって、スキーマの変更に付随して起こる望ましくない処理、例えば意図しない読み込み処理、書き込み処理など、を回避することができ、高い並行性、および、可用性を実現する、最も速く、最も簡単な方法を利用することができます。
以下の場合、最も速くスキーマ変更を行うことができます：
1. NULLプロパティを設定して列を追加
2. NULL、および、デフォルトプロパティを設定して列を追加
3. NOT NULLプロパティをNULLに変更
4. デフォルト制約を追加
5. チェック、または、デフォルト制約を削除
以下の場合は変更に付随して読み込み、または、書き込み処理が発生するため、スキーマ変更の処理は最も遅くなります。
1. チェック制約はチェックを行うためにテーブルの全ての行の読み込みが発生
2. NULLプロパティの列にデフォルト制約と「WITH VALUES」句の組み合わせを適応するとバッチ更新処理が発生
3. NOT NULL制約を（デフォルト制約と共に）追加するとバッチ更新処理が発生
4. NULLプロパティをNOT NULLに変更するとバッチ更新処理が発生
5. データ型、または、データ長を変更するとバッチ更新処理が発生。唯一の例外はvarchar型の長さを拡張した場合。
結論として、パフォーマンス、および、並行性を最も高くするための、大規模なテーブルにおけるスキーマ変更のベストプラクティスはNULLプロパティで列を追加することからスタートします。もし、NULL値を許容できないのであれば、そのアプリケーションはNOT NULLで列を追加しなければならないでしょう。「スキーマ変更の制御（パート2）」では、NULLプロパティで追加された新しい列のバッチ更新処理を分離することによって、パフォーマンスと並行性を最大限に高めること方法を紹介します。そのバッチ更新処理は、パフォーマンス、および、並行性を高くするために、複数の部分的な更新処理に置き換えられます。これは並行性を損なわずに列のデータ型やデータ長を変更するためのテクニックです。
コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。