萬盛學電腦網

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

SQL Server 臨時表創建與使用實戰手冊:從語法到性能優化的完整路徑

臨時表是 T-SQL 開發中處理中間結果、分解複雜邏輯、優化大查詢的核心工具。本文提供從創建語法、查詢刪除、實戰案例、內部機制到性能優化的完整實戰手冊,助你避開常見坑點,寫出高性能、可維護的臨時表代碼。

⚡ 快速語法參考卡

操作局部臨時表 (#)全局臨時表 (##)
創建(顯式)CREATE TABLE #T (Id INT PRIMARY KEY, Val VARCHAR(50));CREATE TABLE ##T (Id INT PRIMARY KEY, Val VARCHAR(50));
創建(SELECT INTO)SELECT * INTO #T FROM Source;SELECT * INTO ##T FROM Source;
查詢SELECT * FROM #T;SELECT * FROM ##T;
刪除DROP TABLE #T;DROP TABLE ##T;
清空數據保留結構TRUNCATE TABLE #T;TRUNCATE TABLE ##T;

🧪 完整實戰案例:分頁處理職工編號

-- 1. 環境準備
DROP TABLE IF EXISTS #Tmp; -- SQL Server 2016+ 語法
CREATE TABLE #Tmp (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    WokNo VARCHAR(50)
);

-- 2. 模擬插入測試數據
INSERT INTO #Tmp (WokNo) VALUES ('EMP001'),('EMP002'),('EMP003'),('EMP004');

-- 3. 逐行處理邏輯(演示游標替代方案)
DECLARE @WokNo VARCHAR(500), @Str NVARCHAR(4000), @Count INT, @i INT = 0;
SELECT @Count = COUNT(DISTINCT WokNo) FROM #Tmp;

WHILE @i < @Count
BEGIN
    -- 動態 SQL 取第 @i+1 行的 WokNo
    SET @Str = N'SELECT TOP 1 @WokNoOut = WokNo FROM #Tmp WHERE ID NOT IN (SELECT TOP ' + CAST(@i AS VARCHAR) + N' ID FROM #Tmp)';
    EXEC sp_executesql @Str, N'@WokNoOut VARCHAR(500) OUTPUT', @WokNo OUTPUT;
    
    SELECT @WokNo AS CurrentWokNo, @i AS LoopIndex;
    SET @i = @i + 1;
END

-- 4. 清理
TRUNCATE TABLE #Tmp;
DROP TABLE #Tmp;

🔬 內部機制深度解析

命名隔離機制

  • 局部臨時表:SQL Server 內部自動追加數字後綴(如 #T____________________00000001),存儲於 tempdb.sys.objects
  • 表名限制:最多 116 字符(預留後綴空間)
  • 同名隔離:不同會話/嵌套層級同名 # 表完全隔離,嵌套存儲過程優先解析自身創建的表

約束支援情況

  • ✅ 支援:PRIMARY KEY、UNIQUE、CHECK、DEFAULT、NOT NULL、IDENTITY
  • ❌ 不支援:FOREIGN KEY(指定時會警告並忽略,表仍創建成功)
  • ❌ 不能被 FOREIGN KEY 引用

自動清理時機總結

  1. 存儲過程結束 → 清理該 SP 內創建的 # 表
  2. 會話結束 → 清理該會話所有其他 # 表
  3. 創建會話結束 + 無活躍引用 → 清理 ## 表

🚀 性能優化 5 大鐵律

  1. 高並發禁用 SELECT INTO:鎖定 tempdb 系統表導致系統級阻塞,改用 CREATE TABLE + INSERT
  2. 大數據量分批提交:避免長事務撐爆 tempdb 事務日志
  3. 索引策略:建表即建索引,大量插入後 UPDATE STATISTICS 刷新統計信息
  4. 及時 DROP/TRUNCATE:長連接中釋放空間,防止 tempdb 膨脹
  5. 表變量優先評估:< 100 行、無索引需求、無並發重編譯 → 用 @Table 替代 #Table

⚠️ 常見坑點避雷指南

  • ❌ 在動態 SQL 字串中引用外層 # 表 → 動態 SQL 獨立作用域看不見,需用全局 ## 表或傳參
  • ❌ 假設 # 表跨會話可見 → 僅限當前會話
  • ❌ 忽略名稱長度限制 → 動態生成表名時留足後綴空間
  • ❌ 事務未提交就 DROP 表 → 隱式提交事務,可能破壞原子性
  • ❌ 用 # 表做 FOREIGN KEY 參考 → 不被支援

核心心法:臨時表是「中間倉庫」,不是「垃圾場」。設計清晰的生命週期、合理的索引、受控的併發訪問,才能發揮其「分而治之、化繁為簡」的最大價值。

copyright © 萬盛學電腦網 all rights reserved