SQL Server 2008 新資料類型 HierarchyId：階層結構儲存與查詢的原生支援

背景與動機

關聯式資料庫處理樹狀階層（組織架構、分類目錄、BOM 物料清單）傳統上採用「遞迴 CTE（Common Table Expressions，SQL 2005+）」或「路徑列舉/巢狀集合」模型。SQL Server 2008 引入 HierarchyId 系統 CLR 類型，將階層位置作為**頭等資料類型**原生支援，簡化開發並提升效能。

核心概念：二進位編碼的路徑

HierarchyId 以變長二進位值儲存節點在樹中的**絕對路徑**。範例：

字串格式為 /父/子/孫/，數字代表同層級下的排序序數（非 ID）。

建立與基本操作

CREATE TABLE OrgChart (
    NodeId HIERARCHYID PRIMARY KEY,
    EmpName NVARCHAR(50),
    Title NVARCHAR(50)
);

-- 插入根節點
INSERT INTO OrgChart VALUES (HIERARCHYID::GetRoot(), N'CEO', N'執行長');

-- 插入子節點：取得父節點的 GetDescendant(null, null) 產生新序數
DECLARE @parent HIERARCHYID = HIERARCHYID::GetRoot();
INSERT INTO OrgChart VALUES (@parent.GetDescendant(NULL, NULL), N'CTO', N'技術長');

-- 查詢某節點的所有子孫
SELECT * FROM OrgChart
WHERE NodeId.IsDescendantOf(@parent) = 1;

-- 查詢某節點的祖先鏈
SELECT * FROM OrgChart
WHERE @node.IsDescendantOf(NodeId) = 1 AND NodeId <> @node
ORDER BY NodeId.GetLevel();

核心方法速查

移動子樹：Reparenting 範例

-- 將 @oldNode 整棵子樹移至 @newParent 底下
DECLARE @oldPath NVARCHAR(4000) = @oldNode.ToString();
DECLARE @newPath NVARCHAR(4000) = @newParent.GetDescendant(NULL, NULL).ToString();

UPDATE OrgChart
SET NodeId = NodeId.GetReparentedValue(
    HIERARCHYID::Parse(@oldPath),
    HIERARCHYID::Parse(@newPath)
)
WHERE NodeId.IsDescendantOf(@oldNode) = 1;

效能與限制考量

• 索引支援：HierarchyId 欄位可建立叢集/非叢集索引，IsDescendantOf、GetAncestor 可利用索引求範圍。
• 移動成本：移動父節點需更新**所有子孫節點**的 HierarchyId 值（資料列級鎖定），頻繁重組的樹不適合（建議改用遞迴 CTE 或圖資料庫）。
• 深度限制：理論無上限，但實務建議 ≤ 100 層，避免二進位值過長影響索引效能。
• 排序序數：GetDescendant 產生的序數非連續整數，插入順序決定同層排序；需業務語意排序時，應另存 SortOrder 欄位。

適用場景建議

• ✅ 組織架構、產品分類、檔案目錄、BOM（變動頻率低、查詢頻繁）
• ❌ 高頻重組的任務依賴圖、社交網路關係（改用 Graph DB 或 Adjacency List + 遞迴 CTE）