テーブルにNotNULL制約の列を追加すると時間がかかるの理由

Question

実現したいこと・問題

約700万件のレコードをテーブルがあり、列を追加する作業をしております。
具体的にNotNull制約を持つ列を5列程追加するのですが。
単純にNotNull制約の列を追加するとエラーになるので、以下の手順で列追加を行っています。

1.NULL許可の列を追加

ALTER TABLE T_URIAGE ADD COLUMN41 VARCHAR(1) NULL;

2.任意の値を登録

UPDATE T_URIAGE SET COLUMN41 = '1';

3.NotNull制約へ変更

ALTER TABLE T_URIAGE ALTER COLUMN COLUMN41 VARCHAR(1) NOT NULL;

実行時間は、1の手順に約1秒、2の手順に約20分、3の手順に約20分ほど掛かります。
この時、3の手順でNotNull制約を付けているだけなのに20分も時間がかかるのはなぜなのでしょうか？

また、上記の手順以外で列追加の実行時間を短縮する方法はあるのでしょうか？

補足情報

実行環境はSQL Server 2016です。

元となるテーブルは以下のようなレイアウトです。

CREATE TABLE [dbo].[T_URIAGE](
	[COLUMN01] [varchar](10) NOT NULL,
	[COLUMN02] [varchar](10) NULL,
	...
	[COLUMN40] [varchar](10) NULL,
 CONSTRAINT [T_URIAGE] PRIMARY KEY CLUSTERED 
	(	
	[COLUMN01] ASC
	) 
)

Answer 1

単純にNotNull制約の列を追加するとエラーになるので

なんでもいいからデフォルト値(SQL Server は空文字OK)を入れます。

ALTER TABLE T_URIAGE ADD COLUMN41 VARCHAR(1) NOT NULL DEFAULT ''

Comments

[negitoro]

ご回答ありがとうございます。
2の手順の値をデフォルト値にすると得たい結果がなりましたし、時間も短縮されました。

Answer 2

データベースによりますが、null 可の列と null 不可の列とでは（ディスク上に格納される）データ構造に違いが出てくるものがあります。

そうなると ALTER TABLE で列の not null 制約を変更することで、その時点でのファイルにデータ構造変更がかかるわけで、それなりに時間がかかってしまう場合もあるかと思います。

1. の手順で20分かかるということは、それだけ大量のデータを持っているのでしょうから、そこにデータ構造変更をすればそれはもちろん大量のディスクアクセスを伴い、遅くなるでしょう。

Comments

[negitoro]

ご回答ありがとうございます。
他の方の回答で変更処理がかかることは分かりましたが、データベースによっても違うことがわかり勉強になりました。

Answer 3

内部的な細かい動きまでは調べてませんが、この記事によると、NULLをNOT NULLに変更したタイミングでバッチ更新処理がかかるみたいですね。

Comments

[negitoro]

ご回答ありがとうございます。
何をやってるのかと思ってたらバッチ更新処理をやってたのですね。