SQL Server 臨時表創建、刪除與正確清理姿勢（含全局/局部、表變量對比）

臨時表是 T-SQL 中處理中間結果的利器，但創建方式、作用域、清理時機若不當，會導致系統表爭用、緩存污染、並發阻塞。本文對比兩種創建語法、演示正確刪除模式、對比表變量，給出生產級最佳實踐。

一、 兩種創建語法

1. 顯式結構創建（推薦：結構清晰、可預建索引）

CREATE TABLE #tmp (
    name NVARCHAR(255),
    id   INT,
    idx_name NONCLUSTERED (name)  -- 臨時表也可建索引
);

2. SELECT INTO 隱式創建（適合：臨時存儲查詢結果、結構頻繁變）

SELECT 
    COUNT(id) AS storyNum,
    SUM(CASE WHEN ISNUMERIC(code)=1 THEN CONVERT(DECIMAL(10,2), code) ELSE 0 END) AS codeNum,
    SUM(CASE WHEN ISNUMERIC(realcode)=1 THEN CONVERT(DECIMAL(10,2), realcode) ELSE 0 END) AS realcodeNum,
    tdtname, cycle, jiracomponent, jirastatename, qualityvalue, storycodellt
INTO #tmp
FROM IKNOW_STORY_U2000V1R7C00
GROUP BY tdtname, cycle, jiracomponent, jirastatename, qualityvalue, storycodellt;

二、 作用域與命名

三、 正確刪除/清理模式（核心）

錯誤示範：直接 DROP TABLE #tmp 若表不存在會報錯；查 sysobjects 判斷錯誤（臨時表在 tempdb，名稱含後綴隨機串）。

標準安全模式：

-- 單條語句：存在則刪，不存在不報錯
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp;

存儲過程結尾統一清理（推薦）：

CREATE PROC dbo.usp_Example
AS
BEGIN
    SET NOCOUNT ON;

    -- 業務邏輯...
    CREATE TABLE #stage (...);
    INSERT #stage ...;

    -- 返回結果
    SELECT * FROM #stage;

    -- 顯式清理（雖然會話結束自動清，但顯式釋放系統表鎖、減少 tempdb 壓力）
    IF OBJECT_ID('tempdb..#stage') IS NOT NULL
        DROP TABLE #stage;
END
GO

四、 臨時表 vs 表變量 選型決策樹

需要統計信息/索引/大數據量/動態 SQL 引用  →  臨時表 (#tmp)
    ├─ 數據量 > 1萬行
    ├─ 需建非聚集索引優化連接/篩選
    ├─ 需在動態 SQL (EXEC/sp_executesql) 中跨批次訪問
    └─ 需並行執行計劃

小數據量/無索引需求/僅當前批次/避免重編譯  →  表變量 (@tmp)
    ├─ 行數 < 1000（經驗值）
    ├─ 無統計信息，優化器假設 1 行，適合嵌套循環連接
    ├─ 不觸發重編譯，存儲過程重用性好
    └─ 事務回滾不影響表變量數據（隱式自主事務特性）

五、 進階技巧：大數據量首次載入用 SELECT INTO 避免日誌爆漲

-- 場景：從大表導入臨時表，數據量大（百萬行+）
-- SELECT INTO 為最小日誌操作（簡單恢復模式/大量日誌模式），極快
SELECT * INTO #big_tmp FROM LargeTable WHERE FilterCond;

-- 後續需建索引再查詢
CREATE NONCLUSTERED INDEX IX_#big_tmp_Key ON #big_tmp (KeyCol);

-- 小數據量或需精確控制類型：CREATE TABLE + INSERT
CREATE TABLE #small_tmp (Col1 INT, Col2 NVARCHAR(50));
INSERT #small_tmp SELECT Col1, Col2 FROM SmallTable;

六、 系統表爭用緩解：TRUNCATE 替代 DELETE 清空臨時表

-- 循環處理中需重置臨時表
-- DELETE 產生大量日誌、鎖升級、觸發器
-- TRUNCATE 只記錄頁釋放、極快、重置自增種子
TRUNCATE TABLE #tmp;  -- 臨時表同樣適用
-- 然後再 INSERT 新批次

掌握臨時表的「生、用、死」全生命周期管理，是寫出高性能、高併發 T-SQL 存儲過程的基本功。