測試環境與方法

• 資料庫：tempdb
• 工具：sp_spaceused、sys.dm_db_file_space_usage、sys.dm_io_virtual_file_stats、sys.dm_os_buffer_descriptors
• 測試表變數：DECLARE @tmp TABLE (list_no INT, id INT) 插入 90 萬行

核心發現

1. 磁碟空間與 IO

• 表變數建立並插入資料期間，tempdb 空間增加 ~9 MB
• sys.dm_io_virtual_file_stats 顯示寫入位元組增加 ~8.3 MB，證實發生實際磁碟 IO
• 批次結束後空間自動釋放

2. 記憶體緩衝池

• 資料頁面會被緩存至 Buffer Pool (sys.dm_os_buffer_descriptors 可見)
• 批次結束後緩存頁面逐漸被驅逐

3. 系統表可見性

• tempdb.sys.objects 可查詢到表變數內部物件 (名稱如 #A1B2C3...)
• 僅在當前批次可見

表變數 vs 暫存表 對比

選型建議

• 小資料量 (< 1 萬行)、簡單邏輯、無需統計資訊 → 表變數
• 大資料量、複雜 JOIN/WHERE、需索引、平行查詢 → 暫存表 #temp
• 存儲過程高併發：表變數減少系統表鎖爭用
• 動態 SQL：暫存表跨批次可見，表變數不可

優化技巧

-- 表變數指定主鑰獲得有序掃描
DECLARE @t TABLE (id INT PRIMARY KEY, val VARCHAR(50));

-- 暫存表建立非叢集索引
CREATE INDEX idx_val ON #t(val);

-- 強制重編譯獲得準確基數估計 (暫存表)
OPTION (RECOMPILE);