-- 單次陳述式完成「刪除 + 歸檔」,原子性、無競態
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.NewColDECLARE @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;
| 特性 | 表變數 @table | 暫存表 #temp |
|---|---|---|
| 作用域 | 批次/存儲程序結束自動清除 | Session 級,手動 DROP 或斷開連線清除 |
| 統計資訊 | 無 (優化器假設 1 行) | 有,支援自動更新 |
| 索引支援 | 僅 PRIMARY KEY / UNIQUE (隱含索引) | 完整索引 (叢集/非叢集/篩選/含檔) |
| 平行查詢 | 不支援 | 支援 |
| 交易回滾 | 不受影響 (隱性提交) | 受交易控制 |
| 重編譯 | 極少 | 可能觸發 (統計變更) |
| 適用資料量 | < 1000 行、小查找表 | 大資料量、複雜查詢、需索引 |
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+ 開發必備工具箱。