SQL Server 暫存表完整操作指南：建立、查詢、索引、作用域與實務模式

暫存表分類與特性

建立語法

顯式 DDL（推薦：可加索引、約束、計算欄位）

CREATE TABLE #TempOrders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME DEFAULT GETDATE(),
    Amount DECIMAL(12,2),
    Status TINYINT CHECK (Status IN (0,1,2)),
    INDEX IX_TempOrders_CustomerID (CustomerID),
    INDEX IX_TempOrders_OrderDate (OrderDate)
)

隱式 SELECT INTO（快速原型、結構同步）

SELECT OrderID, CustomerID, OrderDate, Amount
INTO #TempOrders
FROM Orders
WHERE OrderDate >= '2024-01-01'

不複製索引、約束、Identity、預設值、計算欄位。僅複製欄位定義與資料。

核心操作

索引與統計資料：暫存表效能關鍵

• 建立索引：CREATE INDEX IX_#Temp_CustomerID ON #TempOrders(CustomerID) INCLUDE (Amount)
• 自動統計資料：預設開啟，閾值 500+20% 變更觸發更新
• 手動更新：UPDATE STATISTICS #TempOrders 大量異動後
• 重編譯機制：DDL 後、統計資料變更、OPTION (RECOMPILE) 觸發重編譯，獲得最佳計畫

實務模式：複雜查詢分解

-- 步驟 1：篩選熱門客戶
SELECT CustomerID INTO #HotCustomers
FROM Orders
WHERE OrderDate >= DATEADD(MONTH, -3, GETDATE())
GROUP BY CustomerID
HAVING COUNT(*) > 10

-- 步驟 2：建立索引支援後續 JOIN
CREATE INDEX IX_HotCustomers_CustID ON #HotCustomers(CustomerID)

-- 步驟 3：主查詢 JOIN 暫存表（優化器有統計資料，選擇 Hash Match）
SELECT o.*, c.CustomerName
FROM Orders o
JOIN #HotCustomers hc ON o.CustomerID = hc.CustomerID
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2024-01-01'

-- 步驟 4：清理
DROP TABLE #HotCustomers

進階技巧

跨動態 SQL 共享

-- 僅全域暫存表可跨 EXEC() 邊界
CREATE TABLE ##SharedTemp (ID INT PRIMARY KEY, Data VARCHAR(100))
EXEC('INSERT ##SharedTemp VALUES (1, ''A'')')
SELECT * FROM ##SharedTemp

防重複登入（全域暫存表經典應用）

CREATE PROC CheckUserLogin @UserID VARCHAR(20), @Result BIT OUTPUT
AS
BEGIN
    IF OBJECT_ID('tempdb..##Login_' + @UserID) IS NULL
    BEGIN
        EXEC('CREATE TABLE ##Login_' + @UserID + ' (UserID VARCHAR(20))')
        SET @Result = 0  -- 允許登入
    END
    ELSE
        SET @Result = 1  -- 已登入
END

-- 登出/連線斷開時，全域暫存表自動清理（最後一個工作階段結束）
-- 異常斷電：表殘留但 OBJECT_ID 判斷為不存在（無活動工作階段引用）

暫存表 vs 表變數快速選擇

• 資料 > 1000 筆、需索引、複雜 JOIN、跨程序 → 暫存表
• 資料 < 100 筆、單純運算、極高併發、零鎖需求 → 表變數
• 不確定 → 暫存表（統計資料保護計畫品質）

TempDB 壓力監控

-- 版本存放區、內部物件、使用者物件空間
SELECT * FROM sys.dm_db_file_space_usage WHERE database_id = 2

-- 作用中暫存表大小
SELECT 
    t.name,
    SUM(p.rows) AS Rows,
    SUM(a.total_pages) * 8 / 1024.0 AS SizeMB
FROM tempdb.sys.tables t
JOIN tempdb.sys.partitions p ON t.object_id = p.object_id
JOIN tempdb.sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name LIKE '#%'
GROUP BY t.name
ORDER BY SizeMB DESC