SSMS を使用するとテーブルの列を追加することが可能です。
テーブルに列を追加した際にどのような操作が行われているのかを軽く調べて見たいと思います。
今回は SQL Server 2012 RC0 を使用しています。
■列の追加の方法
SSMS を使用して列を追加するための方法としては 2 種類あるかと思います。
- 列をテーブルの途中に追加する
- 列をテーブルの最後に追加する
どちらの方法で列を追加するかによって、処理内容が変わってきます。
それでは実際にどのような処理が行われるかを見ていきたいと思います。
■列をテーブルの途中に追加する
SSMS を使用するとテーブルの途中に列を追加することが可能です。
以下の表定義になっているテーブルがあります。
このテーブルの既存の列の間に Col4 を追加してみたいと思います。
SSMS のデフォルトの設定では以下のようなメッセージが表示され、変更を保存することができません。
テーブルの既存列の途中に列を追加した場合、以下の処理が実行されます。
- 列を追加後のレイアウトのテープルを [tmp_テーブル名] として作成
- INSERT INTO ~ SELECT ~ WITH (HOLDLOCK TABLOCKX) でデータを挿入
- 元のテーブルをドロップ
- [tmp_テーブル名] として作成したテーブルを元のテーブル名にリネーム
テーブルを新規に作成し、データを移行するという処理が実行されます。
そのためテーブルの再作成が実行されるため、SSMS の標準の設定では既存列の途中に列を追加することができません。
この方法で追加をする場合は SSMS のオプションを変更する必要があります。
# デザインのテーブルの再作成を必要とする変更を~ を無効にする必要があります。
INSERT 中は元のテーブルがロックされますため、同時実行性が大幅に落ちることになります。
■列をテーブルの最後に追加する
この場合は、ALTER TABLE が使用されテーブルのスキーマ変更が行われます。
再作成ではないため SSMS のオプションの変更は必要ありません。
ALTER TABLE dbo.Table_1 ADD |
ただし、上記の変更をデータが入っている状態で実行をすると以下のようなエラーが発生します。
テーブル ‘Table_1’ |
ALTER TABLE を使用した列の追加に関しては、NOT NULL が設定できません。
NULL を許容するか、DEFAULT を設定する必要があります。
NOT NULL を外した状態で実行すると、テーブルの最後に列を追加することが可能です。
NULL 許容として設定した場合、追加した列の情報は以下のようにページに格納されます。
Col4 = [NULL] Slot 0 Offset 0x0 Length 0 Length (physical) 0 |
Offset が 0 の情報として格納がされています。
この場合、テーブルのスキーマ情報のみが更新され、各ページの情報は変更されないようですね。
DEFAULT を設定した場合も同様の格納状態となるようです。
ALTER TABLE dbo.Table_1 ADD |
テーブルの最後に列を追加した場合のログレコードの内容がこちらになります。
データが格納されているページは変更されず、システムテーブルのスキーマ情報が更新されているようです。
列の追加はしないのが好ましいですが、どの位置に追加するかは事前に十分検討しないといけないですね。