既存のテーブルに新しいカラムを追加する際、not nullで追加すると、テーブルのレコード数によってはかなりの時間がかかることが経験上分かっていました。また、null許可で追加すると瞬時に終わることも経験上分かっています。
この時間差についてはドキュメントにも記載があります。
このNULL許可の有無による、カラム追加時の実行速度の違いについて、トランザクションログに記録されるデータの違いを確認することで理解しようと試みました。
以下のサンプルテーブルを使って検証します。
検証環境はSQL Server 2014 Developer Edition で実施しました。
create table sampletable (val int)
insert into sampletable (val) values(1),(2),(3)
select * from sampletable
サンプルテーブルには、3レコード入っています。
トランザクションログの確認方法
select * from sys.fn_dblog(null, null)
トランザクションログは上記クエリを実行することで中身を確認できます。
このクエリだけを実行すると、検証で実行したクエリによるログ書き込み以外のデータも大量に取れてしまうため、checkpointを実行することで中身を（ほぼ）空の状態にします。
checkpoint
select * from sys.fn_dblog(null, null)
このように、3レコードだけが入っている状態になります。
1. nullなカラムを追加
checkpoint
ALTER TABLE sampletable ADD column_int int NULL;
select * from sys.fn_dblog(null, null)
AllocUnitName : 変更が行われたオブジェクト
Operation : 操作の種類
トランザクションログに書き込みが行われたことが分かります。
全てSQL Serverが内部で管理しているオブジェクトへの変更だけで、テーブル自体には変更が行われていないことが分かります。
2. not nullなカラムを追加
checkpoint
ALTER TABLE sampletable ADD column_int int not NULL default(0);
select * from sys.fn_dblog(null, null)
トランザクションログに書き込まれるデータが変化したことは見て取れますが、not nullなカラムを追加しても、テーブル自体へは変更されていないようです。
ドキュメントによると、以下のように、2012 Enterprise Edition以降であれば、not null列の追加でもオンライン操作となったようです。今回、検証環境が2014 Developer Editionだったためオンライン操作になっていました。トランザクションログの内容が変化したのは、規定値をテーブルのメタデータに格納する処理が増えたためと思われます。
NEWID()等を規定値とすると、2012 Enterprise Edition以降でもテーブルへの変更も実施されるとの記載もあったため、実験してみました。
checkpoint
ALTER TABLE sampletable ADD column_newid uniqueidentifier not NULL default(newid());
select * from sys.fn_dblog(null, null)
今度は、テーブルに対して行の修正操作が実施されたことが分かります。
バージョンによってはnot nullでのカラム追加はオフライン操作となり、オンライン操作をサポートしているバージョンでも追加するカラムの型やデフォルト値によってはオフライン操作となり、テーブルのレコードサイズが多いほど実行に時間がかかってしまうため、事前検証しておく必要がありそうです。
3. メタデータの中身を確認してみる
select * from sys.sysschobjs order by created desc
select * from sys.syscolpars where id = 1271675578
カラム追加により変更されたメタデータは、DACで接続するとSELECTすることができます。
sys.sysschobjsは、制約やテーブルなどオブジェクトの管理テーブルのようです。
sys.syscolparsは、テーブルに紐づくカラムを管理しているテーブルのようでした。
まとめ
nullまたはnot nullなカラムをテーブルに追加する際、トランザクションログに書き込まれるデータの違いから、各処理が完了するために必要な時間の違いを理解しました。
null許可 : メタデータのみなので瞬時書き込み（＝オンライン操作）
not null : 2012 Enterprise Edition以降で、ランタイム定数を追加する場合は瞬時書き込み。それ以外はオフライン操作扱いのため、テーブルのレコード数が多ければ多いほど時間がかかる。事前検証必須。