核心差異快速對照
| 特性 | 表變數 | 暫存表 |
|---|
| 儲存位置 | 記憶體（可能換頁到磁碟） | tempdb 磁碟（熱資料會被快取到記憶體） |
| 交易日誌 | 不產生 | 產生（保證 ACID） |
| 索引支援 | 僅隱含主鍵/唯一索引 | 完整支援叢集/非叢集索引 |
| 統計資料 | 無（固定估算 1 筆） | 自動建立/更新統計資料 |
| 執行計畫 | 編譯後固定，無重新優化 | 支援重編譯與參數嗅探 |
| 鎖機制 | 不需要（單一工作階段） | 需要（支援多工作階段存取） |
| 作用域 | 批次 | 工作階段（全域暫存表跨工作階段） |
| 跨程序存取 | 不可 | 可（巢狀程序、動態 SQL） |
表變數深度解析
優勢
- 零日誌、零鎖、零統計資料維護 → 極低系統負載
- 編譯階段確定計畫，避免重編譯開銷
- 適用於極小資料集（< 100 筆）、單純全表掃描、無複雜 JOIN
劣勢
- 無統計資料 → 優化器假設 1 筆資料 → 大資料量時嚴重低估基數 → 選錯計畫（如 Nested Loops 代替 Hash Match）
- 無法建立索引 → 無法支援範圍查詢、排序、群組優化
- 記憶體壓力大時會換頁，效能劇降
- 無法在動態 SQL、巢狀程序間共享
暫存表深度解析
優勢
- 完整索引與統計資料支援 → 複雜查詢最佳化
- 支援跨程序、動態 SQL、多工作階段存取
- 大資料量排序、群組、JOIN 效能穩定
劣勢
- 產生日誌、鎖、統計資料維護開銷
- 可能觸發重編譯（DDL 後、統計資料變更）
- tempdb 壓力大時成為瓶須
選擇決策樹
- 資料量 < 100 筆且單純運算 → 表變數
- 需跨程序/動態 SQL/多工作階段共享 → 暫存表（全域 ## 視需求）
- 涉及複雜 JOIN、排序、群組、篩選 → 暫存表 + 適當索引
- 極高併發、極低延遲要求、資料量小 → 表變數（鎖/日誌為零）
- 資料量大或不確定 → 暫存表（統計資料保護計畫品質）
- 記憶體壓力大 → 傾向暫存表（tempdb 可受資源調控器限制）
實務案例：海運公司效能調優
該公司大量使用暫存表處理臨時資料集，開發人員無法判斷選擇時機。經分析：
- 小筆交易驗證邏輯（< 50 筆）→ 改用表變數，降低 tempdb 負載
- 大量貨單彙總分析（> 10 萬筆）→ 保留暫存表並建立覆蓋索引
- 批次排程跨多個程序共用中繼資料 → 全域暫存表 ##Temp
調優後 tempdb 爭用下降 60%，批次作業時間縮減 35%。
tempdb 調優關鍵提醒
- 多資料檔案（每核心 1 檔，上限 8）減少 PFS/GAM/SGAM 爭用
- 啟用瞬間檔案初始化
- 監控版本存放區、內部物件、使用者物件空間使用率
- 避免長跑交易導致版本存放區暴增
