SQL Server 2008 HierarchyID 階層識別碼資料類型完整實作指南

實驗目標

掌握 SQL Server 2008 新增的 HierarchyID 系統資料類型，以高效方式建模、查詢與操作樹狀結構資料（組織架構、目錄分類、BOM 物料清單等）。

先決條件

• 具備 T-SQL 撰寫經驗與 SSMS 操作熟練度。
• 實驗環境：SQL Server 2008 虛擬機（帳號 administrator / password01!）。

HierarchyID 核心優勢

• 以變長二進位編碼表示節點路徑，儲存極度緊湊。
• 內建豐富方法：GetAncestor()、GetDescendant()、IsDescendantOf()、GetLevel()、ToString()、Parse() 等。
• 支援索引（深度優先/廣度優先），大幅加速子樹查詢與祖先查詢。
• CLR UDT 實現，效能優於遞迴 CTE 或路徑字串模擬方案。

典型應用場景

1. 建立員工組織架構表：Node hierarchyid PRIMARY KEY。
2. 插入子節點：@parent.GetDescendant(@lastChild, NULL) 自動計算新節點編碼。
3. 查詢完整子樹：WHERE Node.IsDescendantOf(@root) = 1。
4. 移動子樹：更新節點編碼為新父節點的後代編碼。

實驗步驟概覽

1. 啟動虛擬機，以 administrator 登入。
2. 開啟 SSMS 連線本機執行個體。
3. 執行 Lab 腳本建立範例資料表、插入測試資料、驗證查詢效能。
4. 觀察執行計畫中 HierarchyID 索引的 Seek 操作。

關鍵程式碼片段

CREATE TABLE OrgChart
(
    Node hierarchyid PRIMARY KEY,
    EmployeeID int UNIQUE,
    Name nvarchar(50),
    Title nvarchar(50)
);

-- 建立深度優先索引
CREATE INDEX IX_OrgChart_DepthFirst ON OrgChart (Node);

-- 插入根節點
INSERT INTO OrgChart (Node, EmployeeID, Name, Title)
VALUES (hierarchyid::GetRoot(), 1, 'CEO', 'Chief Executive Officer');

-- 插入子節點
DECLARE @parent hierarchyid = (SELECT Node FROM OrgChart WHERE EmployeeID = 1);
DECLARE @lastChild hierarchyid = (SELECT MAX(Node) FROM OrgChart WHERE Node.GetAncestor(1) = @parent);
INSERT INTO OrgChart (Node, EmployeeID, Name, Title)
VALUES (@parent.GetDescendant(@lastChild, NULL), 2, 'CTO', 'Chief Technology Officer');

效能優勢總結

HierarchyID 以 O(log n) 複雜度完成祖先/子樹查詢，儲存空間隨樹深線性增長，是 SQL Server 內建處理階層資料的最佳選擇。