SQL Server 提供 表變數 (Table Variable) 與 暫存表 (Temporary Table) 兩種機制儲存暫時資料。雖語法相似,但在**作用域、統計資訊、索引維護、交易行為、參數傳遞**等核心層面有本質差異。本文將系統比較兩者特性、破除常見迷思、提供實測效能數據,並給出具體選擇決策樹。
SQL Server 2000 引入,屬變數一種,以 DECLARE @var TABLE (...) 宣告。定義包含欄位、資料型別、約束 (PK、UNIQUE、NULL、CHECK,**不支援 FK**)。
@t2 = @t1 錯誤)TRUNCATE TABLESET IDENTITY_INSERT ON 插入識別欄位顯式值INSERT INTO @var EXEC proc (SQL 2008+ 支援)DECLARE @tb1 TABLE (Id INT, Name VARCHAR(20), Age INT);
INSERT @tb1 VALUES (1, N'劉備', 22);
SELECT * FROM @tb1;

ALTER TABLE @tb1 ADD CONSTRAINT CN_AccountAge CHECK (Age > 18);

| 類型 | 前綴 | 可見範圍 | 銷毀時機 |
|---|---|---|---|
| 區域暫存表 | # | 目前連線 (Session) | 連線結束、或預存程序結束 (若在 SP 內建立) |
| 全域暫存表 | ## | **所有連線** | 建立者連線結束,且無其他連線參考時 |
-- 連線 1
CREATE TABLE ##temp (RowID INT);
INSERT ##temp VALUES (3);
-- 連線 2 (每 5 秒查詢)
WHILE 1=1 BEGIN SELECT * FROM ##temp; WAITFOR DELAY '00:00:05'; END
-- 連線 1 關閉視窗 → 連線 2 下一次查詢報錯:找不到物件

| 迷思 | 真相 | |
|---|---|---|
| 表變數只在記憶體,暫存表只在磁碟 | **錯誤**。兩者記憶體足夠時皆在記憶體運作,壓力大時皆會寫入 tempdb 磁碟 | |
| 表變數沒有索引 | **錯誤**。宣告時可定義 PK/UNIQUE 建立叢集/非叢集索引,甚至複合索引 | |
| 查詢 sysobjects 找表變數 | **錯誤**。表變數系統名稱非使用者可見,**無法透過系統表查詢** |
| 特性 | 表變數 | 暫存表 |
|---|---|---|
| 作用域 | 目前批次 | 目前連線 (含巢狀 SP),全域跨連線 |
| 建立方式 | 僅 DECLARE | CREATE TABLE / SELECT INTO |
| 名稱長度 | 128 位元組 | 116 字元 |
| 自訂型別/XML Schema | 可用 (目前 DB) | 必須在 tempdb 定義 |
| 排序規則 | 繼承目前資料庫 | 繼承 tempdb |
| 索引建立時機 | 宣告時 (不可事後 DDL) | 隨時可 CREATE INDEX |
| 約束類型 | PK, UQ, NULL, CK (無 FK) | PK, UQ, NULL, CK, DF (無 FK) |
| 事後 DDL | **不可** | **可** |
| INSERT 方式 | INSERT (SQL 2008+ 支援 INSERT EXEC) | INSERT / INSERT EXEC / SELECT INTO |
| IDENTITY_INSERT | 不支援 | 支援 |
| TRUNCATE | 不支援 | 支援 |
| 銷毀方式 | 批次結束自動 | DROP TABLE / 連線結束自動 |
| 交易長度 | 極短 (僅修改瞬間) | 正常交易長度 |
| ROLLBACK 影響 | **不影響** 表變數資料 | **影響** (資料回滾) |
| 重編譯 | 不觸發 | 觸發 |
| 統計資訊 | **不建立** (優化器估計 1 列) | **建立** (依實際列數產生最佳計畫) |
| 當參數傳入 SP | SQL 2008+ 支援 (需預定義 Table Type) | 不支援 |
| 動態 SQL 使用 | 必須在動態 SQL 內宣告 | 可外部預建,動態 SQL 參考 |
Sales.SalesOrderDetail 資料 × 10 = ~1.2M 列@tv 與暫存表 #tmpModifiedDate JOIN 原表| 指標 | 表變數 | 暫存表 |
|---|---|---|
| 邏輯讀取 (IO) | 極高 (掃描) | 低 (索引尋找) |
| 執行時間 | > 50 秒 | ~ 8 秒 |
| 執行計畫 | 估計 1 列 → Nested Loops | 估計準確 → Hash Match |
結論:表變數**無統計資訊**導致優化器嚴重低估基數,選擇嚴重次優計畫。大量資料 JOIN **嚴禁用表變數**。
UPDATE STATISTICS #tmp 或 sp_recompile) 確保後續查詢最佳計畫COLLATE表變數與暫存表各有設計初衷:前者輕量、無交易干擾、適合函數/參數傳遞;後者功能完整、統計資訊完善、適合複雜暫存運算。理解兩者在**統計資訊、作用域、DDL 彈性、交易語意**上的本質差異,並依實際負載測試驗證,才是做出正確架構決策的關鍵。