臨時表是 SQL Server 暫存中間結果、簡化複雜查詢、優化大數據處理的利器。本文系統梳理創建方式、生命週期、作用域差異、手動/自動清理機制、性能選型建議,並提供標準化代碼模板。
| 方式 | 語法 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|---|
| CREATE TABLE + INSERT | 顯式定義結構再插入 | 結構可控、可定義索引/約束、並發安全、計劃緩存友好 | 代碼較長、需預知結構 | 高並發系統、複雜邏輯、需索引優化 |
| SELECT INTO | 查詢結果直接建表 | 極快、自動推導結構、適配動態列 | 鎖定 tempdb 系統表、並發易阻塞、無索引/約束、計劃難復用 | 單語句大數據、臨時探索、低並發 |
-- 1. 顯式創建(推薦生產環境)
CREATE TABLE #TmpTable (
Id INT IDENTITY(1,1) PRIMARY KEY,
Col1 VARCHAR(50) NOT NULL,
Col2 INT DEFAULT 0,
Col3 DATETIME2 DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_TmpTable_Col1 ON #TmpTable(Col1); -- 可加索引
-- 2. SELECT INTO(快速原型)
SELECT Col1, Col2, Col3
INTO #TmpTable
FROM SourceTable
WHERE Condition = 1;
| 前綴 | 名稱 | 可見範圍 | 自動清理觸發條件 | 典型用途 |
|---|---|---|---|---|
# | 局部臨時表 | 僅當前會話 | 會話結束 / 存儲過程結束 | 會話級中間結果、存儲過程內部邏輯 |
## | 全局臨時表 | 所有會話可見 | 創建會話結束 且 所有引用會話結束 | 跨會話共享數據、緩存熱點數據 |
| 無前綴 + tempdb.前綴 | 持久用戶表 | 所有會話可見 | 手動 DROP / 資料庫重啟 | 長期暫存、ETL 中繼、跨作業共享 |
USE TestDB;
GO
-- 1. 創建局部臨時表
CREATE TABLE #TmpStudent (
Tid INT IDENTITY(1,1),
Name VARCHAR(50),
Age INT
);
INSERT INTO #TmpStudent (Name, Age) VALUES ('XiaoWang', 25);
SELECT * FROM #TmpStudent;
-- 2. SELECT INTO 替代寫法
SELECT * INTO #TmpStudent2 FROM Student WHERE Age > 20;
SELECT * FROM #TmpStudent2;
-- 3. 手動刪除
DROP TABLE #TmpStudent;
DROP TABLE #TmpStudent2;
-- 命名規範:tmp_專案_模塊_業務含義
CREATE TABLE tempdb.dbo.tmp_ETL_Order_Cleaned (
OrderId BIGINT PRIMARY KEY,
CleanedData NVARCHAR(MAX),
ProcessedAt DATETIME2 DEFAULT SYSUTCDATETIME()
);
-- 優點:跨作業/跨會話持久、可建完整索引/統計、可被 Agent 作業調度
-- 缺點:需手動清理策略(定期 Job 刪除過期數據)、佔用 tempdb 空間
選型口訣:
並發高、結構固、要索引 → CREATE TABLE #
一次性、數據大、無並發 → SELECT INTO #
跨會話共享、生命週期長 → ## 或 tempdb 永久表
極小數據、無索引、極簡邏輯 → 表變量 @Table