SQL Server 一時テーブル・テーブル変数・共通テーブル式(CTE)

一時テーブル，テーブル変数，共通テーブル式(CTE)の使い方

---

一時テーブル

— 一時テーブルはtempdbに格納される
— 名前の先頭に#1つ：ローカル一時テーブル(セッション内のみ)
— 名前の先頭に#2つ：グローバル一時テーブル(全ユーザーが使用可)

— 一時テーブル作成
SELECT *
 
INTO #tempA –グローバル一時テーブルの場合は ##tempA
 
FROM table1

— 何らかの処理
SELECT * FROM #tempA

— 一時テーブル削除
–通常は自動的に削除されるので必要ないがManagement Studioのクエリウインドウなどで繰り返し実行するときは必要
DROP TABLE #tempA

---

テーブル変数

— 宣言されたスコープ内で有効

— テーブル変数宣言(定義)
Declare
 
@T1 Table(
   
ID int identity(1,1) –ID用に1から順番に番号を付けたい場合
   
, a1 int
   
, a2 nvarchar(50)
   
, PRIMARY KEY (a1)
)

— テーブル変数にデーターを挿入(SELECT ～ INTO文は使用できない)
INSERT INTO @T1 (a1, a2)
 
SELECT a1, a2
 
FROM table1

— 何らかの処理(FROM句以外では別名を使用する必要がある)
SELECT * FROM @T1 AS T

— 自動的に削除されるので削除の必要はない

---

共通テーブル式(CTE)

SELECT文が入れ子になってわけがわからなくなるのを防げる

;WITH CTE1 (a1, a2)
AS
(
    SELECT a1, a2
        FROM Table1
        GROUP BY a1, a2
        –CTE内では ORDER BY は使えない
)

SELECT T1.a1, T2.b1
    FROM CTE1 AS T1
    INNER JOIN Table2 AS T2 ON T1.a1 = T2.a1
    ORDER BY T1.a1

–CTEは直後のクエリーでしか使えないので以降のクエリーではエラーとなる

SELECT * FROM CTE1 –エラー

–カンマで区切れば連続してCTEを参照できる

;WITH
CTE1 (a1, a2)
AS
(
  SELECT a1, a2
  FROM Table1
  GROUP BY a1, a2
),
CTE2 (b1, b2)
AS
(
  SELECT MIN(a1), MAX(a2)
  FROM CTE1 –CTE2の中でCTE1を使用
)
SELECT b1, b2
FROM CTE2

---

参考：
テーブルの作成と変更の基礎(MSDN)
共通テーブル式(MS)
共通テーブル式の使用(MSDN)

Updated：
2009-12-10　共通テーブル式を追加．