SQL Server 2008 新功能總覽 (一)：TVP、日期時間新型別、MERGE、ROWVERSION、CTE、PIVOT/UNPIVOT、GROUPING SETS 實戰範例

1. 表值參數 (TVP) vs 暫存表 vs 表變數 vs CTE

2. sql_variant 萬用型別

DECLARE @v SQL_VARIANT;
SET @v = 123;       -- int
SET @v = N'文字';   -- nvarchar
SET @v = GETDATE(); -- datetime
SELECT SQL_VARIANT_PROPERTY(@v, 'BaseType'); -- 回傳基礎型別

相當於 C# `object`，慎用 (隱含轉換、索引無法使用)。

3. 日期時間新型別

4. MERGE 合併陳述式

MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID
WHEN MATCHED THEN
    UPDATE SET T.Val = S.Val
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Val) VALUES (S.ID, S.Val)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE; -- 目標有、來源無 → 刪除
OUTPUT $action, INSERTED.*, DELETED.*;

• WHEN NOT MATCHED BY TARGET = 來源有、目標無 → `INSERT`。
• WHEN NOT MATCHED BY SOURCE = 目標有、來源無 → `DELETE`/`UPDATE`。

5. ROWVERSION (取代 TIMESTAMP)

CREATE TABLE T (ID INT, RV ROWVERSION);
INSERT INTO T (ID) VALUES (1);
SELECT * FROM T; -- RV 自動產生唯一二進位版本號
UPDATE T SET ID = 2 WHERE ID = 1; -- RV 變更
-- 樂觀並發：WHERE RV = @OriginalRV

6. SYSDATETIME() 高精度時間

SELECT SYSDATETIME();      -- datetime2(7)
SELECT SYSUTCDATETIME();   -- UTC datetime2(7)
SELECT SYSDATETIMEOFFSET();-- datetimeoffset(7)

7. GROUPING SETS / ROLLUP / CUBE 進階彙總

-- 基礎資料
DECLARE @t TABLE (Goods NVARCHAR(20), Sku1 NVARCHAR(10), Sku2 NVARCHAR(10), Qty INT);
INSERT @t VALUES
(N'凡客TX',N'紅',N'S',1),(N'凡客TX',N'黑',N'S',2),(N'凡客TX',N'白',N'L',3),
(N'京東村山',N'白',N'L',4),(N'京東村山',N'紅',N'S',5),(N'京東村山',N'黑',N'L',6),
(N'亞馬遜拖鞋',N'白',N'L',7),(N'亞馬遜拖鞋',N'紅',N'S',8);

-- ROLLUP: 階層彙總 (Goods → Sku1 → Sku2)
SELECT Goods, Sku1, Sku2, SUM(Qty) AS Total
FROM @t
GROUP BY ROLLUP (Goods, Sku1, Sku2);

-- CUBE: 所有組合彙總
SELECT Goods, Sku1, Sku2, SUM(Qty) AS Total
FROM @t
GROUP BY CUBE (Goods, Sku1, Sku2);

-- GROUPING SETS: 自訂彙總層級
SELECT Goods, Sku1, Sku2, SUM(Qty) AS Total
FROM @t
GROUP BY GROUPING SETS ( (Goods), (Goods, Sku1), (Goods, Sku1, Sku2) );

-- 識別彙總列
SELECT
    CASE WHEN GROUPING(Goods)=1 THEN N'[ALL]' ELSE Goods END AS Goods,
    CASE WHEN GROUPING(Sku1)=1 THEN N'[ALL]' ELSE Sku1 END AS Sku1,
    CASE WHEN GROUPING(Sku2)=1 THEN N'[ALL]' ELSE Sku2 END AS Sku2,
    SUM(Qty) AS Total
FROM @t
GROUP BY GROUPING SETS ( (Goods), (Goods, Sku1) ), ROLLUP (Sku1, Sku2);

8. 宣告變數同時賦值 (SQL 2008+)

DECLARE @id INT = 0;           -- 簡潔
-- DBA 建議分開：DECLARE @id INT; SET @id = (SELECT TOP 1 ID FROM T);

9. 共用資料表運算式 (CTE) 遞迴與層級控制

CREATE TABLE EmpTree (EmpID INT PRIMARY KEY, EmpName NVARCHAR(50), ReportsTo INT);
INSERT EmpTree VALUES (1,N'Richard',NULL),(2,N'Stephen',1),(3,N'Clemens',2),
(4,N'Malek',2),(5,N'Goksin',4),(6,N'Kimberly',1),(7,N'Ramesh',5);

-- 遞迴查詢 Stephen 下屬
WITH EmpCTE AS (
    SELECT EmpID, EmpName, ReportsTo, 0 AS Lvl
    FROM EmpTree WHERE EmpID = 2
    UNION ALL
    SELECT e.EmpID, e.EmpName, e.ReportsTo, c.Lvl + 1
    FROM EmpTree e JOIN EmpCTE c ON e.ReportsTo = c.EmpID
)
SELECT * FROM EmpCTE WHERE EmpID <> 2; -- 排除根節點

-- 限制遞迴深度 (MAXRECURSION)
WITH EmpCTE AS (
    SELECT EmpID, EmpName, ReportsTo, 0 AS Lvl
    FROM EmpTree WHERE EmpID = 2
    UNION ALL
    SELECT e.EmpID, e.EmpName, e.ReportsTo, c.Lvl + 1
    FROM EmpTree e JOIN EmpCTE c ON e.ReportsTo = c.EmpID
)
SELECT * FROM EmpCTE WHERE EmpID <> 2 AND Lvl <= 2
OPTION (MAXRECURSION 2);

10. PIVOT (行轉欄) / UNPIVOT (欄轉行)

-- PIVOT: 必須搭配彙總函數
CREATE TABLE #Score (Name NVARCHAR(10), Score INT);
INSERT #Score VALUES (N'張三',97),(N'李四',28),(N'王五',33),(N'神人',78);

SELECT [張三], [李四], [王五]
FROM #Score
PIVOT (AVG(Score) FOR Name IN ([張三],[李四],[王五])) AS P;

-- UNPIVOT: 將多欄轉為 屬性-值 對
CREATE TABLE VendorEmp (VendorID INT, Emp1 INT, Emp2 INT, Emp3 INT);
INSERT VendorEmp VALUES (1,4,3,5),(2,4,1,5);

SELECT VendorID, EmpName, Score
FROM VendorEmp
UNPIVOT (Score FOR EmpName IN (Emp1, Emp2, Emp3)) AS U;

• PIVOT `IN` 清單型別必須一致。
• UNPIVOT 會自動過濾 `NULL` 值列。
• 兩者查詢結尾需加別名 (`AS P` / `AS U`)。