歷程：從 CTE 到原生類型

• SQL 2005：通用資料表表達式 (CTE) 遞迴查詢樹結構，運行良好但屬應用層邏輯
• SQL 2008：引入 **hierarchyid** 系統 CLR 類型，將層次結構提升為**頭等資料類型**

核心概念

• 傳統模式：儲存 **父節點 ID** (相對位置)，移動父節點 = 單行更新，不影響子孫
• hierarchyid：儲存 **絕對路徑位置** (如 /, /1/, /1/1/)，二進制緊湊編碼
• 範例值：0x (根)、0x58 (/1/)、0x5AC0 (/1/1/)

內建方法完整列表

方法	用途	範例
GetAncestor(n)	取得第 n 層祖先	@h.GetAncestor(1) → 父節點
GetDescendant(child1, child2)	取得兩子節點間的**插入位置**	見下文詳解
IsDescendantOf(@h)	判斷是否為某節點後代	WHERE h.IsDescendantOf(@root) = 1
GetLevel()	取得節點層級 (根=0)	@h.GetLevel()
ToString()	二進制 → 可讀路徑字串	/1/2/3/
Parse('/1/2/')	字串 → 二進制 (靜態方法)	hierarchyid::Parse('/1/2/')
GetReparentedValue(old, new)	整棵子樹移動：舊路徑前綴替換為新路徑	移動部門至新父部門
二進制讀寫	Read/Write(BinaryReader/Writer)	支援 .NET 序列化

GetDescendant 獨特語意：回傳「潛在位置」而非現有節點

-- 在父節點 @parent 下新增子節點
DECLARE @lastChild hierarchyid = (
  SELECT MAX(Node) FROM Org WHERE Node.GetAncestor(1) = @parent
);
DECLARE @newNode hierarchyid = @parent.GetDescendant(@lastChild, NULL);
-- @lastChild 為現有最後一個子節點，NULL 表示無下一個兄弟
-- 回傳緊隨 @lastChild 之後的空隙位置

一致性與效能考量

• **父節點變更 = 整棵子樹路徑重寫** (需 GetReparentedValue 批量更新)
• 建議：**父節點變更頻率低** 的場景使用 (組織架構、產品分類、檔案系統)
• 高頻移動場景仍建議傳統 ParentID + 遞迴 CTE / 路徑枚舉 (Materialized Path)

T-SQL 集合操作缺口

目前缺乏標準 SQL 集合式操作範例 (如「取得某節點所有葉子孫節點」)。相關操作更像**程序式命令**而非宣告式查詢，需配合 CTE/遞迴函數完成。

遷移建議

1. 現有 ParentID 表：新增 hierarchyid 欄，編寫遞迴 CTE 回填路徑
2. 建立計算欄 NodePath AS Node.ToString() PERSISTED 便於除錯
3. 關鍵查詢改寫：IsDescendantOf 替代遞迴 CTE，效能提升數量級
4. 新增/移動節點邏輯封裝為預存程序，保證路徑一致性