カラムを末尾や途中に追加するスクリプトの生成

SQL Serverで作成済みのテーブルに対してカラムを追加する場合、末尾に追加するなら簡単にスクリプトを作成できますが、途中に追加する場合は、新しくテーブルを作成してそこにデータを移動し、古いテーブルを削除してから新しく作成したテーブルの名称をリネームするといったことをやらないといけません。

通常は「ALTER TABLE tbl_name ADD column_name1 AFTER column_name2」とかの「AFTER」や「FIRST」とかでできるのですが、なぜか、SQL Serverではこれが使えないので、一時テーブルを使う方法で問題ないかどうかなどの対応方法について頭を悩ます手間があります。

以下、SQL Serverでカラムを末尾に追加したものと、途中に追加したもののそれぞれの変更スクリプトです。

作成済みのテーブル。

末尾に「remark」カラムを追加（保存はしない）。

この状態でManagement Studioのメニュー「テーブル構造 > 変更スクリプトの生成」を選択すると、

変更スクリプトの保存ダイアログが表示されます。

「はい」を選択して、保存場所とファイル名を指定すると、ダイアログ内に表示された変更スクリプトがファイルに出力されます。

実際に出力されたスクリプトは以下の通りです。

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Table_1 ADD
	remark nchar(10) NULL
GO
ALTER TABLE dbo.Table_1 SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

上記のスクリプトはトランザクション処理やその他にも色々な設定が出力されていますが、単純なカラムの追加だけでいいのであれば、以下のスクリプトのみでOKです。

ALTER TABLE dbo.Table_1 ADD	remark nchar(10) NULL

さて、問題のカラムを途中に追加するスクリプトですが、

上記の状態で同様な手順で変更スクリプトを生成すると、以下のようなスクリプトが出力されます。

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Table_1
	(
	id int NULL,
	remark nchar(10) NULL,
	name nchar(10) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Table_1 SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Table_1)
	 EXEC('INSERT INTO dbo.Tmp_Table_1 (id, name)
		SELECT id, name FROM dbo.Table_1 WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Table_1
GO
EXECUTE sp_rename N'dbo.Tmp_Table_1', N'Table_1', 'OBJECT' 
GO
COMMIT

個人で作成したDBや規模の小さなプロジェクトとかならテーブルを作成する方法もいいですが、これが数十万件以上のデータが登録されたテーブルが対象となるとデータ移行に時間がかかるでしょうし、本当に大丈夫なのかとか、失敗したら不味いな...といった不安がどうしても付きまとうので、末尾に追加で問題ないなら、その方が安心です。