萬盛學電腦網

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

SQL Server 暫存表 vs 資料表變數:特性差異、選擇指南與實戰範例

核心差異快速比較

特性暫存表 (#temp / ##temp)資料表變數 (@table)
儲存位置tempdb (磁碟)記憶體優先,壓力大時溢寫 tempdb
交易日誌完整記錄最小記錄 (僅中繼資料)
索引支援完整 (叢集/非叢集/篩選/含含)僅隱含主鍵/唯一索引
統計資料自動建立/更新,最佳化器可用無統計資料,最佳化器假設 1 列
鎖定/並行完整鎖定機制無鎖定,輕量
DDL 修改支援 ALTER TABLE宣告後不可變更結構
存活範圍工作階段 (#) / 全域 (##)批次/程序結束即銷毀
參數傳遞不可作參數可作 OUTPUT 參數、TVP
巢狀範圍可跨巢層存取僅限宣告範圍

暫存表詳細特性

建立與索引

CREATE TABLE #News (
    News_id INT NOT NULL,
    NewsTitle VARCHAR(100),
    NewsContent VARCHAR(2000),
    NewsDateTime DATETIME
);

-- 可事後加入約束與索引
ALTER TABLE #News ADD
    CONSTRAINT DF_NewsDateTime DEFAULT (GETDATE()) FOR NewsDateTime,
    PRIMARY KEY CLUSTERED (News_id);

CREATE NONCLUSTERED INDEX IX_News_Date ON #News(NewsDateTime);

全域暫存表範例

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID('tempdb..##wzg_test') AND type = 'U')
BEGIN
    DROP TABLE ##wzg_test;
    PRINT 'dropped ##wzg_test';
END
ELSE
BEGIN
    CREATE TABLE ##wzg_test (id INT, name VARCHAR(100));
    PRINT 'created ##wzg_test';
END

INSERT INTO ##wzg_test VALUES (1, 'wzh');
SELECT * FROM ##wzg_test;

資料表變數詳細特性

宣告與使用

DECLARE @News TABLE (
    News_id INT NOT NULL,
    NewsTitle VARCHAR(100),
    NewsContent VARCHAR(2000),
    NewsDateTime DATETIME
);

INSERT @News VALUES (1, 'BlueGreen', 'Austen', GETDATE());
SELECT * FROM @News;

限制

  • 不支援 DEFAULT 約束、CHECK 約束、FOREIGN KEY
  • 不支援 ALTER TABLE 修改結構
  • 不支援非叢集索引 (SQL 2014+ 可用記憶體最佳化表變數替代)
  • 統計資料固定為 1 列,大量資料易導致糟糕計畫

完整對比範例:相同邏輯、不同物件

-- 暫存表版本
CREATE TABLE #News (...);
INSERT #News ...;
SELECT * FROM #News;
DROP TABLE #News;

-- 表變數版本
DECLARE @News TABLE (...);
INSERT @News ...;
SELECT * FROM @News;
-- 無需 DROP,離開範圍自動銷毀

選擇決策樹

  1. 資料列數 < 1000 且無複雜查詢/索引需求 → 表變數
  2. 資料列數 > 1000 或需索引/統計資料/複雜聯結 → 暫存表
  3. 需跨程序/動態 SQL 共用 → 全域暫存表 (##)
  4. 需作為參數傳遞給程序 → 表值參數 (TVP) (本質是表變數)
  5. 高並發、低延遲、簡單暫存 → 表變數 (無鎖、無日誌)
  6. 需在交易中回滾暫存資料 → 暫存表 (表變數不參與交易回滾)

效能實測建議

  • 對大量資料 (<10萬列) 進行聯結/排序/群組:暫存表 + 索引 通常快 10-100 倍
  • 小量中繼資料傳遞:表變數避免 tempdb 爭用
  • 監控 tempdb 爭用:PAGEIOLATCH_*、PAGELATCH_* 等待型別

總結

暫存表功能完整、統計完善,適合重型運算;表變數輕量、無鎖、無日誌,適合輕量傳遞。理解兩者機制差異,依資料量、查詢複雜度、並行需求選擇,是 T-SQL 效能調優的關鍵技能。

copyright © 萬盛學電腦網 all rights reserved