萬盛學電腦網

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

SQL Server 2005 兩大新語法實戰:OUTPUT 子句與表變數高效暫存

新語法一:OUTPUT 子句 — 刪除/更新/插入時同步回傳受影響資料

場景:歸檔刪除記錄(移動到歷史表)

-- 單次陳述式完成「刪除 + 歸檔」,原子性、無競態
DELETE FROM CurrentOrders
OUTPUT deleted.OrderID, deleted.CustomerID, deleted.Amount, GETDATE() AS ArchivedAt
INTO ArchiveOrders (OrderID, CustomerID, Amount, ArchivedAt)
WHERE OrderDate < '2023-01-01';

場景:更新前後值對比審計

UPDATE Products
SET Price = Price * 1.1
OUTPUT 
    inserted.ProductID, 
    deleted.Price AS OldPrice, 
    inserted.Price AS NewPrice,
    'PriceAdjust' AS ChangeType,
    GETDATE() AS ChangedAt
INTO PriceHistory (ProductID, OldPrice, NewPrice, ChangeType, ChangedAt)
WHERE CategoryID = 5;

關鍵點

  • inserted:新值(INSERT/UPDATE 後)
  • deleted:舊值(UPDATE/DELETE 前)
  • OUTPUT ... INTO 目標表:導入實體表或表變數
  • OUTPUT ...:直接回傳客戶端(如 SELECT 結果集)
  • 可參考未輸出欄位:OUTPUT deleted.*, inserted.NewCol

新語法二:表變數 — 輕量級暫存結果集

宣告與使用

DECLARE @TopCities TABLE (
    CityID INT PRIMARY KEY,
    CityName NVARCHAR(50),
    OrderCount INT
);

INSERT INTO @TopCities (CityID, CityName, OrderCount)
SELECT TOP 10 CityID, CityName, COUNT(*)
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY CityID, CityName
ORDER BY OrderCount DESC;

-- 後續直接查詢表變數
SELECT * FROM @TopCities WHERE OrderCount > 100;

表變數 vs 暫存表 (#temp) 選擇指南

特性表變數 @table暫存表 #temp
作用域批次/存儲程序結束自動清除Session 級,手動 DROP 或斷開連線清除
統計資訊無 (優化器假設 1 行)有,支援自動更新
索引支援僅 PRIMARY KEY / UNIQUE (隱含索引)完整索引 (叢集/非叢集/篩選/含檔)
平行查詢不支援支援
交易回滾不受影響 (隱性提交)受交易控制
重編譯極少可能觸發 (統計變更)
適用資料量< 1000 行、小查找表大資料量、複雜查詢、需索引

最佳實踐

  • 小結果集、簡單查找、避免重編譯 → 表變數
  • 大資料量、需索引/統計、複雜 JOIN → 暫存表
  • 需跨存儲程序共享 → 暫存表 (##global_temp 跨 Session)

組合招:OUTPUT INTO 表變數 → 再處理

DECLARE @DeletedOrders TABLE (OrderID INT, CustomerID INT, Amount MONEY);

DELETE FROM Orders
OUTPUT deleted.OrderID, deleted.CustomerID, deleted.Amount
INTO @DeletedOrders
WHERE Status = 'Cancelled';

-- 發送通知、寫入日志、更新統計...
SELECT * FROM @DeletedOrders; -- 後續邏輯使用

結語

OUTPUT 子句將「資料變更」與「結果捕獲」合為原子操作,消除「先查再刪」的競態與雙掃描開銷。表變數 提供輕量暫存,配合 OUTPUT INTO 形成高效資料流管線。兩者是 SQL Server 2005+ 開發必備工具箱。

copyright © 萬盛學電腦網 all rights reserved