萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 表變數 vs 暫存表:深度比較與選擇決策指南 (含效能實測)

前言:兩大暫存資料容器的正面對決

SQL Server 提供 表變數 (Table Variable) 與 暫存表 (Temporary Table) 兩種機制儲存暫時資料。雖語法相似,但在**作用域、統計資訊、索引維護、交易行為、參數傳遞**等核心層面有本質差異。本文將系統比較兩者特性、破除常見迷思、提供實測效能數據,並給出具體選擇決策樹。

一、表變數

定義與語法

SQL Server 2000 引入,屬變數一種,以 DECLARE @var TABLE (...) 宣告。定義包含欄位、資料型別、約束 (PK、UNIQUE、NULL、CHECK,**不支援 FK**)。

關鍵特性

  • 作用域:僅限目前批次 (Batch),**不跨越** 預存程序/函數/觸發器邊界
  • 重編譯:較暫存表**少觸發** 預存程序重編譯
  • 交易/鎖定/記錄:僅資料修改期間生效,持續時間極短,**ROLLBACK 不影響** 表變數內容
  • 自動清除:批次結束自動銷毀

限制事項

  • 不可指派給另一變數 (@t2 = @t1 錯誤)
  • 約束/預設值/計算欄位**不可參考** 使用者自訂函數
  • 不可為約束命名
  • 不可 TRUNCATE TABLE
  • 不可 SET IDENTITY_INSERT ON 插入識別欄位顯式值
  • SQL 2000/2005:不可 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 下一次查詢報錯:找不到物件

全域暫存表連線關閉後報錯

限制事項

  • 名稱上限 116 字元 (引擎自動附加唯一後綴)
  • 不可分區
  • 不可建立 FK 約束
  • 自訂型別/ XML Schema 集合必須在 **tempdb** 中預先定義 (啟動預存程序或修改 model DB)
  • 同名約束/索引在高併發下易衝突 → **建議不命名,讓系統自動產生唯一名稱**

三、三大常見迷思破解

表變數建立索引範例

DECLARE @MyTable TABLE (RowID INT PRIMARY KEY CLUSTERED);
DECLARE @temp TABLE (
  RowID INT NOT NULL, 
  ColA INT NOT NULL, 
  ColB CHAR(1) UNIQUE,
  PRIMARY KEY CLUSTERED (RowID, ColA)  -- 複合叢集索引
);

四、核心差異對照表 (決定選擇的關鍵)

迷思真相
表變數只在記憶體,暫存表只在磁碟**錯誤**。兩者記憶體足夠時皆在記憶體運作,壓力大時皆會寫入 tempdb 磁碟
表變數沒有索引**錯誤**。宣告時可定義 PK/UNIQUE 建立叢集/非叢集索引,甚至複合索引
查詢 sysobjects 找表變數**錯誤**。表變數系統名稱非使用者可見,**無法透過系統表查詢**
特性表變數暫存表
作用域目前批次目前連線 (含巢狀 SP),全域跨連線
建立方式僅 DECLARECREATE 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 列)**建立** (依實際列數產生最佳計畫)
當參數傳入 SPSQL 2008+ 支援 (需預定義 Table Type)不支援
動態 SQL 使用必須在動態 SQL 內宣告可外部預建,動態 SQL 參考

五、效能實測:JOIN 場景下的驚人差距

測試環境

  • AdventureWorks Sales.SalesOrderDetail 資料 × 10 = ~1.2M 列
  • 分別載入表變數 @tv 與暫存表 #tmp
  • 以 ModifiedDate JOIN 原表

結果

指標表變數暫存表
邏輯讀取 (IO)極高 (掃描)低 (索引尋找)
執行時間> 50 秒~ 8 秒
執行計畫估計 1 列 → Nested Loops估計準確 → Hash Match
結論:表變數**無統計資訊**導致優化器嚴重低估基數,選擇嚴重次優計畫。大量資料 JOIN **嚴禁用表變數**。

六、選擇決策樹

  1. 需在函數 (UDF) 使用 → 表變數 (暫存表不可用於函數)
  2. 需作為 SP 參數傳入 (SQL 2008+) → 表變數 (需建立 User-Defined Table Type)
  3. 資料列數極少 (< 100) 且無複雜 JOIN → 表變數 (低開銷、無重編譯)
  4. 需事後建立索引/修改結構 → 暫存表
  5. 需 TRUNCATE / IDENTITY_INSERT暫存表
  6. 大量資料、複雜 JOIN、需準確統計資訊 → 暫存表
  7. 需跨預存程序/觸發器共享資料 → 區域暫存表
  8. 需跨連線/Session 共享 → 全域暫存表 (謹慎使用)
  9. 動態 SQL 參考外部暫存資料 → 暫存表
  10. ROLLBACK 需保留暫存資料 → 表變數

七、最佳實務總結

  • **預設選擇暫存表**,除非明確符合表變數適用場景 (UDF、TVP、極小資料集、需 ROLLBACK 保留)
  • 暫存表**不命名約束/索引**,避免高併發衝突
  • 大量資料載入暫存表後,**更新統計資訊** (UPDATE STATISTICS #tmp 或 sp_recompile) 確保後續查詢最佳計畫
  • 表變數用於 JOIN 前,**務必先評估列數**,超過百列建議改暫存表
  • 排序規則衝突時,在查詢或宣告中顯式指定 COLLATE

結語

表變數與暫存表各有設計初衷:前者輕量、無交易干擾、適合函數/參數傳遞;後者功能完整、統計資訊完善、適合複雜暫存運算。理解兩者在**統計資訊、作用域、DDL 彈性、交易語意**上的本質差異,並依實際負載測試驗證,才是做出正確架構決策的關鍵。

copyright © 萬盛學電腦網 all rights reserved