SQLデータベースでデータ処理を行う際、一時的なデータ保存が必要になることがあります。クエリ結果を一時的に保存して、そのデータに対してさらなる操作を行う必要がある場合があります。

一時的なデータ保存（論理的な一時テーブル、必ずしもデータベース上の物理的一時テーブルとは限らない）には、主に以下の3つの方法があります。

1. CTE（Common Table Expression）を使用する方法（SQL:2005以降）:

CTE（WITH句）は実際に一時テーブルを作成するわけではなく、サブクエリの結果に名前を付ける機能です。SQL文の可読性を向上させるために使用されるほか、UNION ALLの異なる部分で同じデータを参照する場合に特に有効です。UNION ALLの各部分で同じクエリを実行する場合、コストが高くなるため、CTEを使用すれば一度だけ実行すれば済みます。

例：

WITH 商品カテゴリ階層1 AS
(
    SELECT * FROM category 
    WHERE cgtype = 2
    AND parentid = @親カテゴリID
),
商品カテゴリ階層2 AS
(
    SELECT c.* FROM category c
    INNER JOIN 商品カテゴリ階層1 s
    ON c.parentid = s.cgid
    AND c.cgtype = 3
),
商品カテゴリ階層3 AS
(
    SELECT c.* FROM category c
    INNER JOIN 商品カテゴリ階層2 t
    ON c.parentid = t.cgid
    AND c.cgtype = 4
)

注意：上記のコードで、WITH句の前に「DECLARE @親カテゴリID INT = 1234」と記述する場合、セミコロン（;）で終了させる必要があります。さもなければエラーが発生します。

補足：CTEで定義されたテーブル名が2回以上参照される場合、オプティマイザは自動的にそのデータを一時テーブルに保存します。一度しか参照されない場合は保存されません。また、MATERIZEヒントを使用すると、CTEのデータを強制的にグローバル一時テーブルに保存できます。

2. 一時テーブルを使用する方法

一時テーブルは永続テーブルと似ていますが、Tempdbデータベース内に作成される点が異なります。一時テーブルはデータベース接続が切断されたり、DROP TABLEコマンドが実行されたりするまで存在し続けます（一時テーブルは通常、DROP TABLE操作なしで2回目の作成が行えません）。一時テーブルを作成するとSQL Serverシステムログが生成されますが、Tempdbに割り当てられているため、ユーザーが指定したディスク上ではファイルが見えません。

一時テーブルはローカルとグローバルの2種類に分かれます。ローカル一時テーブルは「#」を接頭辞とし、現在のユーザーセッション内でのみ有効です。ユーザーがインスタンスから切断されると削除されます。グローバル一時テーブルは「##」を接頭辞とし、作成後はすべてのユーザーから参照可能です。そのテーブルを参照しているすべてのユーザーが切断されると削除されます。

例：

IF OBJECT_ID('tempdb..#一時カテゴリ') IS NOT NULL DROP TABLE #一時カテゴリ
CREATE TABLE #一時カテゴリ(
    連番 INT,
    カテゴリ名 VARCHAR(50),
    カテゴリID INT,
    パートナーID INT,
    更新日時 DATETIME,
    操作者 VARCHAR(50)
)

補足：一時テーブルの特性：

1. 列の追加、変更、削除が可能です。列名、長さ、データ型、精度、小数点以下の桁数、NULL許容属性などは変更できますが、いくつかの制限があります。

2. 主キーと外部キー制約の追加や削除が可能です。

3. UNIQUE制約、CHECK制約、DEFAULT定義の追加や削除が可能です。

4. IDENTITYまたはROWGUIDCOL属性を使用した列の追加や削除が可能です。ROWGUIDCOL属性は既存の列に追加したり削除したりできますが、テーブル内には常に1列しか持てません。

5. テーブルとその列は全文インデックスとして登録できます。

3. 表変数を使用する方法

表変数の作成構文は一時テーブルに似ていますが、作成時に名前を指定する必要があります。表変数は変数の一種で、ローカル表変数は「@」を接頭辞とし、現在のユーザーセッション内でのみアクセス可能です。グローバル表変数は「@@」を接頭辞とし、通常はシステム変数（@@Errorや@@RowCountなど）に使用されます。

例：

DECLARE @ニューステーブル TABLE 
( 
    ニュースID INT NOT NULL, 
    タイトル VARCHAR(100), 
    内容 VARCHAR(2000), 
    投稿日時 DATETIME 
)

上記3つの方法の比較:

一時テーブルと表変数の比較:

1. 表変数はメモリ上に保存され、アクセス時にログが生成されませんが、一時テーブルではログが生成されます。

2. 表変数では非クラスター化インデックスを使用できません。

3. 表変数ではDEFAULT値や制約を使用できません。

4. 一時テーブルの統計情報は完全で信頼性がありますが、表変数の統計情報は信頼性が低いです。

5. 一時テーブルにはロック機構がありますが、表変数にはロック機構がありません。

一時テーブルと表変数の選択基準:

1. 表変数を使用する主な考慮点は、アプリケーションに対するメモリの負荷です。コードの実行インスタンスが多い場合、メモリ使用量に特に注意が必要です。小さなデータや計算結果には表変数が適しています。大きなデータ結果で、グループ化や集計を伴わない一時的な計算には表変数も検討できます。

2. 大きなデータ結果や、統計情報を活用して最適化を行う場合は、一時テーブルの使用を推奨します。一時テーブルはインデックスを作成できるほか、Tempdbのストレージスペースを調整することでパフォーマンスを向上できます。

CTEとWITH句の組み合わせによるSQLクエリ性能の向上:

CTEは表変数よりもはるかに効率的です！

表変数は実際には一時テーブルを使用するため、追加のI/Oオーバーヘッドが発生します。そのため、データ量が多く頻繁にクエリを行う状況には向いていません。

注：ストアドプロシージャ内のローカル一時テーブルはトランザクションレベルです。

1. SQL文を動的に組み立てない方法

SELECT * INTO #一時商品 FROM [dbo].[Sys_Product]; SELECT * FROM #一時商品;

2. #で始まる一時テーブルはトランザクションレベルであり、一つのトランザクション内でのみ存在します。