前言：超越基礎 INSERT/UPDATE/DELETE/SELECT

掌握 SQL Server 進階 DML/DQL 語法，能用更少的代碼解決複雜業務：分頁取樣、分組統計、歸檔清理、審計追蹤。本文深度解析 TOP、WITH TIES、COMPUTE、OUTPUT、$IDENTITY/$ROWGUID、WRITE 子句等實戰技巧。

一、 INSERT 進階技巧

1.1 僅插入預設值

INSERT INTO dbo.log_info DEFAULT VALUES;

適用於所有欄位皆有 DEFAULT 約束或允許 NULL 的表，快速生成測試數據或觸發器測試。

1.2 SSMS 查詢視窗快捷鍵

• Ctrl + 0 (數字鍵 0)：在可為 NULL 的欄位輸入 NULL。
• Esc：撤銷編輯，恢復原值。

二、 UPDATE / DELETE 限制影響範圍

2.1 TOP (N) / TOP (N) PERCENT

-- 只更新前 3 筆 (配合 ORDER BY 確定順序)
UPDATE TOP (3) dbo.log_info SET info1 = '1001' WHERE info1 IS NULL;

-- 更新前 80% 筆數
UPDATE TOP (80) PERCENT dbo.log_info SET info2 = '1002' WHERE info2 IS NULL;

-- 刪除前 100 筆舊資料 (歸檔常用)
DELETE TOP (100) FROM dbo.audit_log WHERE log_time < '2023-01-01';

⚠️ 注意： TOP 配合 ORDER BY 需用子查詢或 CTE，否則順序不保證：

-- 正確：按時間刪除最舊 100 筆
DELETE FROM dbo.audit_log 
WHERE id IN (SELECT TOP (100) id FROM dbo.audit_log ORDER BY log_time ASC);

三、 SELECT 進階分組統計

3.1 TOP ... WITH TIES：包含並列名次

-- 假設 id 相同視為並列
SELECT TOP (5) WITH TIES * FROM test ORDER BY id DESC;
-- 若第 5 名有多筆相同 id，全部返回，結果可能 > 5 筆

適用於「前 N 名並列皆獲獎」類業務，避免任意截斷造成不公。

3.2 GROUP BY 進階：CUBE / ROLLUP / GROUPING SETS

-- 建立測試資料
CREATE TABLE SalesInfo (City NVARCHAR(50), OrderDate DATE, OrderID INT);
INSERT INTO SalesInfo VALUES
(N'北京','2014-06-09',1001),(N'北京','2014-08-09',1002),(N'北京','2013-10-09',1009),
(N'大連','2013-08-09',4001),(N'大連','2013-10-09',4002),(N'大連','2013-05-12',4003),
(N'大連','2014-11-11',4004),(N'大連','2014-12-11',4005);

-- CUBE：所有維度組合的彙總 (城市×年份、城市總計、年份總計、總計)
SELECT City, YEAR(OrderDate) AS OrderYear, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY City, YEAR(OrderDate) WITH CUBE;

-- ROLLUP：層級彙總 (城市×年份 → 城市總計 → 總計)
SELECT City, YEAR(OrderDate) AS OrderYear, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY City, YEAR(OrderDate) WITH ROLLUP;

-- GROUPING SETS：自訂彙總組合 (僅城市總計 + 總計)
SELECT City, YEAR(OrderDate) AS OrderYear, COUNT(OrderID) AS OrderCount
FROM SalesInfo
GROUP BY GROUPING SETS ((City, YEAR(OrderDate)), (City), ());

輔助函數： GROUPING_ID(City, OrderYear) 判斷該列是否為彙總行 (1=彙總, 0=明細)，配合 CASE 產生「總計」標籤。

3.3 GROUP BY ALL：保留無資料分組 (已棄用，勿用新專案)

-- 即使 WHERE 過濾掉，仍顯示城市行 (Count=0)
SELECT City, COUNT(OrderID) FROM SalesInfo WHERE City = N'大連' GROUP BY ALL City;

⚠️ ALL 不支援 CUBE/ROLLUP、HAVING 會導致失效，SQL Server 2012+ 標記棄用，建議用 LEFT JOIN 搭配數字表實現。

四、 COMPUTE / COMPUTE BY：舊式報表輸出 (已棄用)

這兩子句在 SQL Server 2012 標記棄用，現代報表請用 GROUP BY ROLLUP、WITH ROLLUP、OVER() 窗口函數或應用層處理。僅供維護舊系統參考：

-- COMPUTE：顯示明細 + 總計 (兩個結果集)
SELECT City, OrderDate, OrderID FROM SalesInfo COMPUTE COUNT(OrderID);

-- COMPUTE BY：分組明細 + 各組小計 + 總計 (多結果集)
SELECT City, OrderDate, OrderID FROM SalesInfo ORDER BY City COMPUTE COUNT(OrderID) BY City;

五、 OUTPUT 子句：DML 操作的「黑盒記錄器」

OUTPUT 子句可捕獲 INSERTED、DELETED 虛擬表數據，用於：審計日誌、同步異構系統、取回自增 ID、異常回滾參考。

5.1 INSERT 取回自增 ID (替代 SCOPE_IDENTITY)

DECLARE @NewIds TABLE (Id INT, CreatedAt DATETIME2);
INSERT INTO dbo.Orders (CustomerId, Amount, CreatedAt)
OUTPUT INSERTED.Id, INSERTED.CreatedAt INTO @NewIds
VALUES (1001, 99.99, SYSDATETIME()), (1002, 149.50, SYSDATETIME());
SELECT * FROM @NewIds; -- 獲得剛插入的 ID 與時間

5.2 UPDATE 記錄修改前後值

DECLARE @Audit TABLE (OrderId INT, OldAmount DECIMAL(10,2), NewAmount DECIMAL(10,2), ModTime DATETIME2);
UPDATE dbo.Orders
SET Amount = Amount * 1.1
OUTPUT INSERTED.Id, DELETED.Amount, INSERTED.Amount, SYSDATETIME() INTO @Audit
WHERE OrderDate >= '2024-01-01';
SELECT * FROM @Audit;

5.3 DELETE 歸檔被刪除資料

DELETE FROM dbo.Orders
OUTPUT DELETED.* INTO dbo.Orders_Archive
WHERE OrderDate < '2020-01-01';

六、 $IDENTITY / $ROWGUID / .WRITE() 大欄位部分更新

• $IDENTITY：引用自增欄位別名，SELECT $IDENTITY FROM tbl 等同 SELECT Id FROM tbl。
• $ROWGUID：引用 ROWGUIDCOL 標記的 uniqueidentifier 欄位，合併複寫常用。
• .WRITE()：大數據類型 (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) 部分更新，避免整欄重寫產生大量日誌與鎖升級。

  UPDATE dbo.Documents
  SET Content.WRITE(N'新增段落', NULL, 0) -- 追加 (NULL 表示末尾，0 表示不刪除)
  WHERE DocId = 1;
  
  UPDATE dbo.Documents
  SET Content.WRITE(N'替換文字', 100, 10) -- 從位移 100 開始，刪除 10 字元，插入新文字
  WHERE DocId = 1;

七、 總結與現代替代對照表

舊技巧	現代標準寫法	適用版本
TOP (N) 無 ORDER BY	CTE + ROW_NUMBER() / OFFSET-FETCH	2005+ / 2012+
COMPUTE / COMPUTE BY	GROUP BY ROLLUP / GROUPING SETS / 窗口函數 SUM() OVER()	2008+ / 2012+
GROUP BY ALL	LEFT JOIN 數字表 / 生成序列	2008+
@@IDENTITY / SCOPE_IDENTITY()	OUTPUT INSERTED.Id	2005+
大字段全量更新	.WRITE() 部分更新	2005+

掌握這些進階語法，能讓你的 T-SQL 代碼更簡潔、效能更可控、審計更完整。建議在新專案中優先採用現代標準寫法，僅在維護舊系統時理解舊語法語意。