SQL Server 臨時表使用全攻略：建立、刪除、生命週期與效能考量

建立方式

方式一：CREATE TABLE (推薦，可控結構)

-- 區域臨時表
CREATE TABLE #TmpStudent (
  Tid INT IDENTITY(1,1) PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Age INT NULL
);

-- 全域臨時表
CREATE TABLE ##GlobalTmp (
  Id INT PRIMARY KEY,
  Data VARCHAR(100)
);

方式二：SELECT INTO (快速、推導結構)

SELECT * INTO #TmpStudent FROM Student WHERE Age > 20;
-- 等同於 CREATE TABLE #TmpStudent + INSERT ... SELECT

差異：SELECT INTO 鎖定 Tempdb 系統表 (sysobjects, sysindexes, syscolumns) 較久，高併發易阻塞；CREATE TABLE 僅鎖定極短時間，適合併發環境。大量單次載入用 SELECT INTO 較快。

方式三：直接在 Tempdb 建立 (持久化至重啟)

CREATE TABLE tempdb.dbo.PersistentTemp (Id INT PRIMARY KEY);
-- 不以 # 開頭，服務重啟前持續存在，需手動清理

刪除與清理

DROP TABLE #TmpStudent;        -- 顯式刪除區域臨時表
DROP TABLE ##GlobalTmp;        -- 顯式刪除全域臨時表
TRUNCATE TABLE #TmpStudent;    -- 清空資料保留結構 (較 DELETE 快、不記錄逐列日誌)

生命週期與可見性

關鍵行為細節

• 巢狀預存程序：子程序建立的 #tmp 對父程序同名 #tmp 遮蔽；子程序結束自動刪除，父程序繼續使用自己的 #tmp。
• 同名衝突：同一 Session 內不可建立同名區域臨時表 (除非巢狀層級不同)。
• 全域臨時表引用計數：Session A 建立 ##G，Session B 查詢中。A 結束後，B 查詢完成前 ##G 仍存在；B 完成後自動刪除。
• 權限：任何使用者可建立臨時表；存取他人建立的全域臨時表需權限。

效能與最佳實務

1. 預先定義結構：併發高用 CREATE TABLE，避免系統表鎖爭用。
2. 加索引：大量資料 JOIN/篩選前建立非叢集索引 (CREATE INDEX IX_#Tmp_Col ON #Tmp(Col))，統計資訊自動建立。
3. 避免 SELECT * INTO 大表：記錄檔膨脹、鎖久。分批 INSERT ... SELECT 或使用最小記錄 (WITH (TABLOCK) + 簡單復原模式)。
4. 及時清理：長執行程序中間結果用完即 DROP，釋放 Tempdb 空間與鎖。
5. 表變數替代小資料集：<100 列、無 JOIN 大表、需避免重編譯時優先考慮表變數。

常見錯誤與排查