SQL Serverで一時テーブルの作成と削除の方法

SQLの実行結果を一時的にどこかに保存したい場合があります。サブクエリで1か月の売上データを集計して、複数の箇所で使い回したい時、毎回SQLを発行しているとパフォーマンス的にボトルネックとなることがあります。そのような場合は、SQL Serverの一時テーブルを使用して、一時テーブルに売上集計結果を保存しておくことでリソースを最小限に抑えることができます。

特に大きなデータ、1000万以上のレコードを保持するテーブルへのクエリでは効果を発揮するかと思います。集計したサマリー結果を一時テーブルに保存しておくことで、毎回膨大なレコードにアクセスする必要がないからです。

概要
ローカル一時テーブルの作成方法
グローバル一時テーブルの作成方法
一時テーブルに主キーやINDEX、データを追加する
一時テーブルの削除方法

概要

一時テーブルの保存場所は、「tempdb」に格納されます。これはデータベース→システムデータベース→tempdbに存在します。

一時テーブルには「ローカル一時テーブル」と「グローバル一時テーブル」が存在しています。ローカルを作成する場合は、先頭に「#（シャープ）」を付けます。作成したユーザの現在の接続（セッション）でのみアクセス可能です。作成したユーザがインスタンスから切断されると削除されます。

グローバル一時テーブルは、テーブル名の先頭に「##」（シャープ・シャープ）を付けます。作成するとすべてのユーザからアクセスすることができます。作成したグローバル一テーブルを参照するすべてのユーザがインスタンスから切断されると、削除されます。

ローカル一時テーブルの作成方法

CREATE TABLE #一時テーブル名(
    テーブル定義
)

サンプル

CREATE TABLE #TEMPTABLE(
     NO      int NOT NULL
    ,YMD     varchar(6) NULL
    ,SALES   decimal(10,2) NOT NULL
)

テーブル名の前に「#」を付けて、CREATE TABLE命令で一時テーブルを作成します。

グローバル一時テーブルの作成方法

CREATE TABLE ##一時テーブル名(
    テーブル定義
)

サンプル

CREATE TABLE ##TEMPTABLE(
     NO      int NOT NULL
    ,YMD     varchar(6) NULL
    ,SALES   decimal(10,2) NOT NULL
)

テーブル名の前に「##」を付けて、CREATE TABLE命令で一時テーブルを作成します。

SQL Serverで使用できるデータ型は以下を参照

SQL Serverデータ型

一時テーブルに主キーやINDEX、データを追加する

一時テーブルに一般的なテーブルのようにキーやインデックスを追加することができます。

一時テーブルに主キーの設定

ALTER TABLE 一時テーブル名 ADD PRIMARY KEY CLUSTERED 
(
    NO ASC
)

サンプル

ALTER TABLE #TEMPTABLE ADD PRIMARY KEY CLUSTERED 
(
    NO ASC
)

一時テーブルにインデックスを設定する方法

CREATE NONCLUSTERED INDEX インデックス名 ON 一時テーブル名
(
 対象のカラム
)

サンプル

CREATE NONCLUSTERED INDEX TEMPORARY_YMD_INDEX ON #TEMPTABLE
(
 YMD ASC
)

PRIMARY KEYの設定がない場合でもINDEXは追加することは可能です。

一時テーブルにデータを追加する

/*通常のINSERT発行*/
INSERT INTO #TEMPTABLE Values(1,'2020/01',5000.00)
SELECT * FROM #TEMPTABLE

/*別テーブルをレコードをINSERTする*/
INSERT INTO #TEMPTABLE SELECT * FROM TABLE

一時テーブルの削除方法

「SQL Serverのインスタンス」から切断すると自動的に一時テーブルは削除されますが、意図的に削除することもできます。

IF OBJECT_ID(N'tempdb..#TEMPTABLE', N'U') IS NOT NULL
DROP TABLE #TEMPTABLE;

カテゴリー:SQLServerデータベース

タグ:SQLServerデータ型