SQL Server で一時テーブルを使う時のちょっとした注意点

SQL Server の一時テーブル

SQL Server の一時テーブルには、テーブル名が ## で始まるグローバル一時テーブルと # で始まるローカル一時テーブルがあります。

今回はローカル一時テーブルをストアードプロシージャ内で使う際の、ちょっとした注意点を取り上げます。

ローカル一時テーブルは、同じセッション内のみ有効なテーブルで、普通のテーブルと同様に CREATE TABLE や SELECT INTO などのステートメントで作成することができます。

実際には、ローカル一時テーブルの場合は SQL Server がわかるように名前を変更して tempdb に生成されています。

例えば、#Test という名前のローカル一時テーブルを作ると、次のような感じで tempdb に生成されます。

データ量の多いテーブル達から、複雑なクエリーで一度に値を取得するよりも、一時テーブルに対象となりそうなデータのみを一旦保存して、その一時テーブルに対してクエリーを書いたほうが、処理時間が速くなることが多いです。クエリーチューニングの際はお試しください。

まず、同じバッチやストアードプロシージャ内で、同じ名前のローカル一時テーブルを作成しようとするとエラーになります。

ローカル一時テーブルは、同じセッション内で有効、と書きましたが、あるストアードプロシージャ A から別のストアードプロシージャ B を実行した時には、A で作成したローカル一時テーブルに B 内で利用できます。

例えば、次のようにストアードプロシージャ uspTempTest1 内で作った #Test テーブルを uspTempTest2 で UPDATE することができます。

呼ばれた側のストアードプロシージャだけ見た時に、ローカル一時テーブルがどのように定義されていて何が入っているのかわからないので、あまりおすすめの使い方ではありません。

ここからが本題で、ちょっと注意しておいたほうが良い点です。

呼ばれる側のストアードプロシージャ内で、同じ名前のローカル一時テーブルを生成してもエラーにはなりません。

先ほどの uspTempTest1 はそのままで、uspTempTest2 内で同じ名前で定義が少し違うローカル一時テーブル #Test を作成して SELECT してみましょう。

uspTempTest1 を実行するとこのような結果になります。

同じ名前のローカル一時テーブルが既に存在していてもエラーにはならず、uspTempTest2 内での SELECT では uspTempTest2 内で作られた #Test が使われていますね。

次に、uspTempTest2 で #Test に値を INSERT してみましょう。

再度 uspTempTest1 を実行するとこのようなエラーになります。

先ほどの SELECT では、uspTempTest2 で生成した #Test が使われていましたが、その真下の INSERT では、uspTempTest1 で生成した #Test が使われた為、TestName というカラムがないというエラーになっています。

マイクロソフトによると、このような状況で、同じ名前のローカル一時テーブルが存在している時に、クエリーがどちらの一時テーブルを使うかは定義されていません。その為、どちらのローカル一時テーブルが使われるかわからないのです。

また、このエラーメッセージからでは、ローカル一時テーブルの名前が原因だと特定するのに時間がかかるかもしれません。

同じセッション内で、同じ名前のローカル一時テーブルを意図的に複数作りたい状況が思い浮かばないので、CREATE でエラーが出てくれたらわかりやすいのになぁ、と思います。

複数人数で開発していて、自分の作ったストアードプロシージャを他の人が作ったストアードプロシージャから実行したり、されたりするような場合は、一時テーブルの名前がかぶらないようにご注意くださいね。