SQL Server で本番テーブルにカラムを追加する時、業務SEが気をつける3つのこと

みなさんこんにちは！ヒロポンです！

「このテーブルにカラム1個足しといて」。

軽く言われるんですよね。でも本番運用中のテーブルだと、これが地味に怖い。ALTER TABLE ... ADD COLUMN 一発で業務が止まることがある。

開発環境の空っぽのテーブルなら一瞬で終わる。でも sqlserver カラム追加 を本番の数百万行に流したら、ロックで全業務が固まった——。業務SEあるある、ですよね。

この記事では、本番テーブルにカラムを足す時に踏む罠を3つに絞って出します。NOT NULL とデフォルト値・Sch-M ロック・列の型。最後に、本番を止めずに足す安全手順もコピペで置いときます。

📌 前提: 挙動は SQL Server のバージョンで変わります(特に 2012 でメタデータ操作が拡張)。この記事は 2016 / 2019 を基準に書いてます。コードは Docker 上の SQL Server 互換エンジンで実機確認済みです。

忙しい人向けに最初にまとめ

• 罠1: NOT NULL + デフォルト値 — 条件が揃えばメタデータのみで即時。でもエディション(Enterprise以外)・型・非定数デフォルトでは全行更新が走って長時間ロック。
• 罠2: Sch-M ロック — ALTER TABLE は「スーパーロック」を取り、テーブルへのアクセスを完全に止める。大テーブルで全行更新が走るとその間ずっと業務停止。
• 罠3: 列の型 — 可変長NULLやcomputed列はメタデータのみ。固定長や非定数デフォルトは全行に書き込みが走る。
• 安全手順: NULL許可で追加 → デフォルトをバッチで段階的に埋める → 必要ならNOT NULL化。

⏱ 対処目安サマリ

着手前に「どれが何分で潰せるか」を先に。深夜メンテの計画に。

罠	主な症状	⏱ 対処目安
1. NOT NULL + default	大テーブルで ADD が終わらない	20分 (定数defaultか確認・分割)
2. Sch-M ロック	ADD 中に全業務が固まる	30分 (低優先ロック or メンテ枠)
3. 列の型	想定外の行サイズ増・全行更新	15分 (型と default を見直す)

1. NOT NULL + デフォルト値の組み合わせで挙動が割れる

まず、いちばん誤解されてるやつ。「NOT NULL のカラムを既存行のあるテーブルに足したら、全行に書き込みが走って遅い」。

これ、半分正解で半分間違いです。ほんまにそうなん？？ってとこを詰めます。よく「SQL Server 2012 以降なら、定数デフォルト付きの NOT NULL カラムはメタデータのみで即時」と言われる。条件が揃えば、確かにそうなる。

-- 既存行があるテーブルに NOT NULL + 定数デフォルトを足す
CREATE TABLE dbo.受注 (受注ID int PRIMARY KEY, 金額 int);
INSERT INTO dbo.受注 VALUES (1, 1000), (2, 2000);
GO

-- 条件が揃えば、定数デフォルト付き NOT NULL はメタのみで即完了(エディション等に依存・後述)
ALTER TABLE dbo.受注 ADD 状態 tinyint NOT NULL DEFAULT 0;
GO

SELECT 受注ID, 状態 FROM dbo.受注;   -- 既存行にも 0 が入って見える

うまくハマれば、既存行にも 0 が入って見えるのに、実際には全行を物理的に書き換えてない。デフォルト値をメタデータとして覚えておいて、読むときに見せてるだけ。だから100万行でもいい感じに一瞬で終わります。

ん？じゃあ何が「半分間違い」なんや。この即時最適化、効かない条件がある。

いちばん見落としやすいのがエディションです。この最適化は Enterprise 系で効きやすい。一方で Standard など一部の環境では、定数デフォルトでも全行更新が走ることがある。実際、手元の検証エンジン(非Enterprise相当)で50万行のテーブルに足したら、ページ数が倍増して全行に書き込みが走りました。

エディション以外でも、デフォルトが定数じゃない(関数を使う)・一部のデータ型・古いバージョン(2008以前)だと全行更新になる。こうなると、その間ずっとロックです。

一行教訓: 2012以降だから一瞬、を鵜呑みにしない。エディション・型・デフォルト次第で全行更新が走る。安全手順で確かめろ。

2. 大テーブルへの ADD COLUMN で Sch-M ロックが走る

罠1で「全行更新が走る」と書きました。その時に何が起きてるか。スキーマ修正ロック(Sch-M)です。これが2つめの、いちばん怖い罠。

裏付けに『Pro SQL Server Internals』を引くと、このロックの正体がはっきり書いてある。

スキーマ修正 (Sch-M) ロックは、メタデータの変更の前に取得される。このロック種別は「スーパーロック」と考えてよい。他のどのロック種別とも互換性がなく、そのオブジェクトへのアクセスを完全にブロックする。排他 (X) ロックと同様に、Sch-M ロックはトランザクションの終わりまで保持される。DDL を明示的トランザクション内で実行する時は、これを念頭に置く必要がある。

「他のどのロックとも両立しない・アクセスを完全にブロック」。ここが効きます。普通の更新ロックは、別の行なら同時に触れる。でも Sch-M は違う。そのテーブルを触ろうとする全員が、ADD COLUMN が終わるまで待たされる。

メタデータのみで終わるなら Sch-M は一瞬なので問題ない。問題は全行更新が走る大テーブル。その数十秒〜数分、業務アプリの SELECT すら全部止まる。え、SELECT も止まるん？？ってなるけど、これが現実です。俺も昔、これ知らずに大テーブルへ ADD して業務が詰まったことがある。これが「カラム足したら本番が固まった」の正体。

判断フローにするとこんな感じ。

ちなみに SQL Server 2014 以降は、ALTER TABLE に WAIT_AT_LOW_PRIORITY というオプションがある。待ち時間を区切って、既存のトランザクションを邪魔しすぎない方法です(ProSql p.489)。とはいえ、まず安全なのは「全行更新を起こさない」こと。次の罠3と安全手順で詰めます。

3. 可変長・固定長・computed 列で挙動とサイズが変わる

3つめは、列の「型」によって挙動が変わる話。

ざっくり言うと、既存行に物理的に書き込みが走るか走らないかが型で分かれます。

◎ 軽い / ○ 良い / △ 条件つき / × 重い / ! 要注意。

-- 可変長NULLは即時・既存行のサイズも据え置き(値が入るまで増えない)
ALTER TABLE dbo.受注 ADD 備考 varchar(200) NULL;

-- computed 列は実体を持たず、読むたびに計算される(PERSISTED 指定時を除く)
ALTER TABLE dbo.受注 ADD 税込 AS (金額 * 1.1);
GO

SELECT 受注ID, 税込 FROM dbo.受注;   -- 金額から都度計算された値が返る

固定長(int や char(n))を非NULLで足すと、行のサイズが即増える。可変長は値が入るまで増えない。この差は断片化やページ分割にも効いてきます。

一行教訓: 後から足す任意項目は可変長NULL、導出値はcomputed。物理列を増やす前に型を選べ。

安全な手順 — 本番を止めずにカラムを足す3ステップ

ここまでの罠を全部避ける、汎用の安全手順です。大テーブルで「NOT NULL + デフォルト」を足したい時でも、これなら止まりません。

-- ステップ1: まず NULL 許可で追加(メタデータのみ・ロックは一瞬)
ALTER TABLE dbo.受注 ADD 区分 tinyint NULL;
GO

-- ステップ2: デフォルト相当をバッチで段階的に埋める(大テーブルは分割して)
UPDATE TOP (5000) dbo.受注 SET 区分 = 0 WHERE 区分 IS NULL;
-- ↑ 区分 IS NULL が無くなるまで繰り返す(1回5000行ずつなら長時間ロックしない)

-- ステップ3: 全行埋まったら、必要なら NOT NULL 化
ALTER TABLE dbo.受注 ALTER COLUMN 区分 tinyint NOT NULL;

ポイントは、長時間の Sch-M ロックを避けて、短いロックの繰り返しに分解すること。ステップ1の ADD ... NULL はメタデータのみで一瞬。ステップ2は普通の UPDATE なので、5000行ずつなら他の業務と共存できる。ステップ3の NOT NULL 化も、全行が埋まっていれば検証は速い。こんな感じで、1回の長いロックを短いロックの繰り返しに割るわけです。

「2012以降なら定数defaultで即時なんでしょ?」と思うかもですが、それが効く条件を毎回確認するより、止まらない手順を体で覚えておくほうが事故りません。深夜メンテ枠が取れない現場だと、特にこれが効きます。

まとめ — カラム1個でも、本番では手順で守る

本番テーブルへのカラム追加で踏む罠は、根っこは1つ。ALTER TABLE が Sch-M ロックでテーブル全体を止めうること。

• NOT NULL + default — 条件が揃えば即時、エディション/型/非定数defaultで全行更新
• Sch-M ロック — 全アクセスを止めるスーパーロック。大テーブルの全行更新で長時間化
• 列の型 — 可変長NULL/computedは軽い、固定長非NULLは重い

迷ったら、NULL許可で足して、バッチで埋めて、必要ならNOT NULL化。この3ステップを手癖にしておけば、「カラム足したら本番落とした」は避けられます！！

よくある質問

Q1. SQL Server 2012以降なら NOT NULL + デフォルトはいつも即時ですか?

条件が揃えばメタデータのみで即時です。ただし、エディション(Standard 等)・非定数デフォルト・一部のデータ型では全行更新が走ります。条件が読み切れない時は、安全手順(NULL許可→バッチ埋め→NOT NULL化)に倒すのが安全です。

Q2. ADD COLUMN 中に SELECT も止まるんですか?

全行更新が走るケースでは止まります。ALTER TABLE が取る Sch-M ロックは他のどのロックとも両立せず、SELECT が取ろうとする Sch-S ロックともぶつかるためです。メタデータのみで終わるケースなら一瞬なので、実用上は気になりません。

Q3. 大テーブルにどうしても NOT NULL 列を即足したい時は?

メンテナンス枠を取って実行するのが王道です。それが難しければ、SQL Server 2014以降の WAIT_AT_LOW_PRIORITY で待ち方を制御するか、この記事の安全手順で短いロックに分解します。一発の長時間 DDL を避けるのが基本方針です。

Q4. computed 列はディスクを使いますか?

PERSISTED を付けない限り、実体を持たず読むたびに計算するのでディスクは使いません。既存列からの導出値(税込など)なら computed 列が軽くて安全です。集計を高速化したい時だけ PERSISTED + インデックスを検討します。

次に読むべき記事

• SQL Server UPDATE … FROM SELECT 3パターン — 安全手順ステップ2の「バッチで埋める」更新の書き方を深掘り
• 業務SEがSQL Server INDEX 断片化に手を出す前に見る3箇所 — 固定長カラム追加で起きる行サイズ増・ページ分割の続き
• SQL Server tempdb スピルを業務SEが本番で踏む3箇所 — 同じ「本番で止まる」系のもう一つの定番
• SQL Server DISTINCT の3つの罠 — 本番で結果や性能が化ける SQL の罠シリーズ
• 「客先常駐しかない」と感じてる業務SEへ — こういう DB 寄りの地力を1つ持つと、現場での立ち位置が変わる話

本番のカラム追加でビビってる同業がいたら、この記事ぶん投げてやってください。どんどんシェア待ってるぜ！！

以上！

この記事の参考文献

• 『Pro SQL Server Internals』 Dmitri Korotkevitch 著 (Apress, 2016) — 引用ページ: p.480, p.489
  SQL Server の内部構造(ストレージ / インデックス / トランザクション / ロック / 統計情報)を DBA レベルで深掘りした技術書。この記事の「Sch-M ロックがテーブルへのアクセスを完全に止める」「WAIT_AT_LOW_PRIORITY で待ち方を制御する」という根拠は同書 Chapter 23(Schema and Low-Priority Locks)に拠っています。本番運用の性能問題を根本から理解したい業務SE / DBA 向けのバイブル。

執筆者

バイブス父さん — 業務 SE 7 年 (SIer 正社員 2 / フリーランス 5)。現職は SEO 直轄部の AI アドバイザー兼 PL、副業で中小 SIer の CTO。SIer の正社員からフリーランスに転じ、複数のエージェント経由で案件を回してきた経験ベースで「業務 SE 視点」の技術 + キャリア記事を書いています。

🐦 X: @hiro_progra0524 (日々の現場メモ更新中)
📝 About Me で経歴詳細を見る