SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

テーブルに列を追加した際の操作内容を調べてみる

leave a comment

SSMS を使用するとテーブルの列を追加することが可能です。

テーブルに列を追加した際にどのような操作が行われているのかを軽く調べて見たいと思います。

今回は SQL Server 2012 RC0 を使用しています。

■列の追加の方法


SSMS を使用して列を追加するための方法としては 2 種類あるかと思います。

  • 列をテーブルの途中に追加する
  • 列をテーブルの最後に追加する

どちらの方法で列を追加するかによって、処理内容が変わってきます。

それでは実際にどのような処理が行われるかを見ていきたいと思います。

 

■列をテーブルの途中に追加する


SSMS を使用するとテーブルの途中に列を追加することが可能です。
以下の表定義になっているテーブルがあります。
image

このテーブルの既存の列の間に Col4 を追加してみたいと思います。
image

SSMS のデフォルトの設定では以下のようなメッセージが表示され、変更を保存することができません。
image

テーブルの既存列の途中に列を追加した場合、以下の処理が実行されます。

  1. 列を追加後のレイアウトのテープルを [tmp_テーブル名] として作成
  2. INSERT INTO ~ SELECT ~ WITH (HOLDLOCK TABLOCKX) でデータを挿入
  3. 元のテーブルをドロップ
  4. [tmp_テーブル名] として作成したテーブルを元のテーブル名にリネーム

テーブルを新規に作成し、データを移行するという処理が実行されます。
そのためテーブルの再作成が実行されるため、SSMS の標準の設定では既存列の途中に列を追加することができません。
この方法で追加をする場合は SSMS のオプションを変更する必要があります。
# デザインのテーブルの再作成を必要とする変更を~ を無効にする必要があります。
image

INSERT 中は元のテーブルがロックされますため、同時実行性が大幅に落ちることになります。

■列をテーブルの最後に追加する


SSMS では列をテーブルの最後に追加することも可能です。
image

この場合は、ALTER TABLE が使用されテーブルのスキーマ変更が行われます。
再作成ではないため SSMS のオプションの変更は必要ありません。

ALTER TABLE dbo.Table_1 ADD
    Col4 nchar(10) NOT NULL
GO

ただし、上記の変更をデータが入っている状態で実行をすると以下のようなエラーが発生します。

テーブル ‘Table_1’
– テーブルを修正できません。
ALTER TABLE では、NULL を許可する列または DEFAULT 定義が指定されている列しか追加できません。
追加する列が ID 列またはタイムスタンプ列の場合、または前述の条件のいずれも満たされない場合は、
この列を追加できるようにテーブルは空である必要があります。列 ‘Col4’ はこれらの条件を満たしていないため、
空でないテーブル ‘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
    Col4 nchar(10) NULL
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    DF_Table_1_Col4 DEFAULT N’AAAA’ FOR Col4
GO

テーブルの最後に列を追加した場合のログレコードの内容がこちらになります。
image

データが格納されているページは変更されず、システムテーブルのスキーマ情報が更新されているようです。

列の追加はしないのが好ましいですが、どの位置に追加するかは事前に十分検討しないといけないですね。

Written by masayuki.ozawa

12月 20th, 2011 at 8:08 am

Posted in SQL Server

Tagged with

Leave a Reply

*