今回はSQL Serverで一時テーブルを作成してみることにします。
ご興味がある方は、よろしければお読みいただければと思います。
一時テーブルとは?
一時テーブルとは、名前の通りある一定期間の間だけ有効なテーブルのことです。通常のテーブルは永続的にデータを保持しますが、一時テーブルは一時的にデータを保持したいときに使用します。また、通常のテーブルと同様にインデックスを付与することができたりするのも特徴です。
メモリで処理するには大きすぎたり、プログラムを組むのが大変だったりする場合には、一時テーブルを作成し処理に必要なデータを投入した上でsqlを使い簡易的に処理するなどが考えられると思います。実装の幅が広がるので、とても便利な機能です。
一時テーブルは2種類ありますので、それぞれ紹介していきたいと思います。
ローカル一時テーブルの作成方法
まずは、一時テーブルの種類の1つであるローカル一時テーブルを作成する方法を紹介します。ローカル一時テーブルの作成方法は、通常のテーブルとほぼ同じです。以下のような構文を使用して作成することができます。
少し分かりづらいのですが、一時テーブルの作成時はテーブル名の先頭に「#」(シャープ)付けることで作成されます。
CREATE TABLE #ローカル一時テーブル名 ( 列名1 データ型, 列名2 データ型, ・・・ )
グローバル一時テーブルの作成方法
次にグローバル一時テーブルを作成する方法を紹介します。グローバル一時テーブルは、ローカル一時テーブルの作成方法とほぼ同じです。
今度はテーブル名の先頭に「##」(シャープ)2個を付けることで作成されます。
CREATE TABLE ##グローバル一時テーブル名 ( 列名1 データ型, 列名2 データ型, ・・・ )
ローカル一時テーブルを実際に作ってみる
それでは実際に一時テーブルを作成しみます。今回は代表として、ローカル一時テーブルを作成してみます。グローバル一時テーブルとローカル一時テーブルは、有効期間を除いて作成の仕方や使い方はほぼ同じですのでグローバル一時テーブルの作成例は割愛します。
以下のように、IDと名前を持つ簡易な構成でローカル一時テーブルを作成してみます。
CREATE TABLE #local_table1 ( id int PRIMARY KEY, name varchar(20) );
すると、SSMSで確認してみたところシステムデータベースのtempdbの一時テーブル欄に上記のテーブルが作成されています。
作成したローカル一時テーブルは通常のテーブルのようにデータを登録して、検索することができます。
それでは、ローカル一時テーブルとグローバル一時テーブルで何が異なるかを次で紹介します。
ローカル一時テーブルとグローバル一時テーブルの違い
それでは、簡単に両者の違いを以下に示します。
ローカル一時テーブル
ローカル一時テーブルは、作成したユーザーのみ使用できるテーブルとなります。そのため、あるユーザーAが作成した場合に他ユーザーBはユーザーAが作成したローカル一時テーブルにはアクセスすることができません。
また、ローカル一時テーブルはユーザーがセッションを切断すると自動的に削除されます。
グローバル一時テーブル
グローバル一時テーブルはローカル一時テーブルの上記制限を緩和していて、あるユーザーAが作成した場合でも他ユーザーBも使用できます。
グローバル一時テーブルは、参照するすべてのユーザーのセッションが切断されたときに削除されます。具体的には、グローバル一時テーブルを作成したセッションが終了し、そのグローバル一時テーブルを参照していた最後のTransact-SQLステートメントの完了時に削除されます。
補足
最後にちょっと補足します。ローカル一時テーブルは作成したユーザーのみ使用できるテーブルとなるのは上記で述べた通りですが、そのような性質上同時にローカル一時テーブルを作成しても特に問題にはなりません。テーブル名は同じですが、SQL Server側で連番を付与してそれぞれ別々のセッションで作成したものとして管理してくれます。
反対にグローバル一時テーブルはすべてのユーザーで使用できる性質を持っているため、同時に作成することはできません。もし、作成しようとした場合は後に実行された方はエラーとなります。
おわりに
いかがでしたでしょうか。一時テーブルを普段業務で使用している人たちは当たりまえのようなことばかりですが、知らない人からするとそんなものを作れるのかと驚くのではないかと思います。
今回はこの辺で失礼いたします。最後までお読みいただきありがとうございました。