SQL Server(MSDE2000) ： ALTER TABLE

質問

SQL Server(MSDE2000) ： ALTER TABLE した項目に対し、直後に UPDATE で値をセットできない

既存のテーブルを仕様変更するため、列を追加し、初期値で埋める SQL 文を書こうとしていますが、うまく動かず、エラーとなってしまい、原因が判らず困っています。

サンプル SQL 文：
------
CREATE TABLE TEST_TABLE ( TEST_FIELD1 SMALLINT, TEST_FIELD2 SMALLINT)
INSERT INTO TEST_TABLE VALUES( 1, 2 )
ALTER TABLE TEST_TABLE ADD TEST_FIELD3 SMALLINT
UPDATE TEST_TABLE SET TEST_FIELD3 = 3
------
※テスト毎に、必ず DROP TABLE TEST_TABLE されている事が前提です。

上記 SQL 文のうち、１行目～３行目までを抜粋して実行すると、ちゃんと CREATE TABLE され、INSERT され、ALTER TABLE される事を確認しました。

ところが、４行目までを一気に実行しようとすると、

------
SQL実行中に以下のエラーが発生しました。
エラーコード：207 [Microsoft][ODBC SQL Server Driver][SQL Server]列名 'TEST_FIELD3' は無効です。
SQLステータス：S0022
------

となってしまい、UPDATE で初期値を埋める事ができません。
しかも、UPDATE に失敗するどころか、２行目の INSERT から以降が結果に反映されなくなるという状況に陥ってしまいます。

また、既存のテーブルの仕様変更が目的なので、その状況に近づけるために、まず、

------
CREATE TABLE TEST_TABLE ( TEST_FIELD1 SMALLINT, TEST_FIELD2 SMALLINT)
INSERT INTO TEST_TABLE VALUES( 1, 2 )
------

を実行し、既存のテーブル（とレコード内容）が存在する状態を作り出された事を、ツール等で確認してから、

------
ALTER TABLE TEST_TABLE ADD TEST_FIELD3 SMALLINT
UPDATE TEST_TABLE SET TEST_FIELD3 = 3
------

の２行を実行してみると、やはり UPDATE は失敗し、前述と同じエラーが発生します。
またこの場合、ALTER TABLE の実行結果も反映されていません。（つまり、TEST_FIELD3 が列追加されていない）

もちろん、ALTER TABLE だけを実行した場合には、ちゃんと列は追加されます。
その後に、UPDATE を実行すれば、ちゃんと追加列に初期値がセットされます。

どうやら、「一回の SQL 文の実行の中で、ALTER TABLE によって新設した列に対しては、UPDATE などでのアクセスはすぐにはできない」のではないか？という状況のようなのです。一回の SQL 文の実行の中において、何らかのトランザクション動作っぽい挙動を感じます。

つまり、ALTER TABLE で追加された列は、その時点ではまだ完全にシステムに認知されていないため、直後の UPDATE 文で認識できずに失敗するのではないか？と。そして、そこでのエラー発生が、ロールバック的に実行した処理をキャンセルしてしまうため、結果として、ALTER TABLE が実行されなかった事になったり、INSERT が実行されなかった事になったりしているのではないか？と思う次第です。

考えられる回避策としては、SQL 文を別々に作成し、個別に実行すれば良いだけの事なのですが、できれば、SQL 文一つにまとめたいと考えています。

どなたか、こういった現象に対する原因・理由の説明、或いは回避策など、何か情報をお持ちの方はいらっしゃいませんでしょうか？

宜しくお願いします。

回答No.1

jamshid6 · ベストアンサー

SQL文を実行するときには、まずコンパイルして、実行プランを立て、それから実行するというステップがあるという点をご理解ください。

私の理解ではその時点でTEST_TABLEが存在しないかぎり、CREATE～INSERT～ALTER～UPDATEはエラーにならないはずです。
ただし、"既にTEST_TABLE(ALTER前）がある状態"で４行一緒に実行すると、

列名 'TEST_FIELD3' は無効です。

と返されますので、それと混同されているのではないかなと推測します。
（CREATE TABLE文は既にテーブルが存在していても、コンパイル時にはエラーにはならず、実行時にエラーになるため。
一方、UPDATE文は後述の通り、コンパイル時にエラーになります）

CREATE～INSERTが成功したあとで、ALTER～UPDATEが失敗するのは、実行に失敗しているのではなく、コンパイルに失敗しているということです。
（「実行して失敗し、ロールバックされた」わけではありません）
既にそのテーブルが存在する場合、SQL Serverはコンパイル時にテーブルのカラムをチェックします。
この時点のテーブルにはTEST_FIELD3はありませんので、コンパイルエラーになります。

一方、実行時にそのテーブルが存在しない場合は、遅延解決と言ってコンパイル時にはカラムがチェックされません。
（実行時に条件がそろっていなければエラーとなります）
従って、CREATE～INSERT～ALTER～UPDATEと一気に実行すると、チェックすべきテーブルがまだ存在していないため、エラーにはなりません。

もう一つ注意すべきなのは、キャッシュの存在です。
一度「UPDATE TEST_TABLE SET TEST_FIELD3 = 3」が成功すると、このクエリはプランキャッシュというところにキャッシュされ、必要に応じて再利用されます。
その状態でテーブルをDROPし、CREATE～INSERTを行い、ALTER～UPDATEを行うと、キャッシュされた情報を参照してTEST_FIELD3は存在すると判断され、コンパイルが通りますので、今度はエラーになりません。

ご参考までに。

お礼

早速の回答、ありがとうございました。

頂いた回答の中の「コンパイル→実行プラン立て→実行」というステップのお話からヒントを得て、問題を解決する方法が見つかりました。

まず、プランキャッシュの存在の事が全く頭に無かったため、UPDATE を実行する時点で、オプティマイザがまだ「TEST_FIELD3」の存在に気が付いていないため（直前で ALTER TABLE で追加される事なんて、コンパイル時点でオプティマイザが知る由もありませんよね）、当たり前のようにエラーになる事に気が付いていませんでした。

しかも、完全に DROP TABLE した状態からでは、提示した４行の実行ではエラーが発生せず、コンパイルが通ってしまう事も、当方のテスト不足で、気が付いていませんでした。

ご指摘を頂いたポイントを参考にし、解決方法を模索した結果、以下のような方法で、当方の問題が解決できた事をご報告します。

まず、

--------
CREATE TABLE TEST_TABLE ( TEST_FIELD1 SMALLINT, TEST_FIELD2 SMALLINT)
INSERT INTO TEST_TABLE VALUES( 1, 2 )
--------

を先に実行しておいた状態で、

--------
DECLARE @SQLSTR VARCHAR(64)

SET @SQLSTR = 'UPDATE TEST_TABLE SET TEST_FIELD3 = 3'

ALTER TABLE TEST_TABLE ADD TEST_FIELD3 SMALLINT
EXEC (@SQLSTR)
--------

というSQL文を実行します。
もうお気付きかと思いますが、コンパイル時にエラーとなってしまっていた UPDATE 文を、文中で動的に組み立てた事にしてしまい、EXECで実行した訳です。
「動的に組み立てられたSQLを実行する際は、実行時にコンパイルされる」という特性を利用してみました。
これによって、当方の問題を解決する事ができました。

ご回答頂いた内容が、かなりのヒントになりました。非常に助かりました。

それと、今回の指摘を受けて思った事は、プランキャッシュの影響を受けるようなSQL文をデバッグする際には、実行毎に、SQLServerのサービスを停止・再開させて強制的にプランキャッシュを破棄した方が、実行結果がプランキャッシュの影響を受けずに済むという点です。ここについては、今後も注意したいと思います。

当然ながら、ベストアンサーとさせて頂きます。

ありがとうございました！