SQL Server 2008 hierarchyid 資料類型：階層式資料儲存與查詢全攻略

SQL Server 2008 引入 hierarchyid CLR 使用者定義型別，專為樹狀結構（組織架構、目錄分類、BOM 物料清單等）設計。它以緊湊二進位編碼表示節點路徑，內建豐富方法，讓遞迴查詢變得簡潔高效。本文詳細說明建立、插入、查詢祖先/子孫、移動子樹等核心操作，並附完整方法速查表。

基本概念與儲存格式

• hierarchyid 值以 / 開頭與結尾，中間以數字與斜線表示層級路徑，如 /1/2/3/。
• 根節點為 /。
• 二進位儲存極為緊湊，索引效能優於傳統遞迴 CTE 或巢狀集合模型。

建立範例資料表

CREATE TABLE dbo.Employees
(
    OrgId      HIERARCHYID NOT NULL,
    EmployeeId INT         NOT NULL,
    Name       VARCHAR(50) NOT NULL,
    Title      VARCHAR(50) NOT NULL,
    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeId),
    CONSTRAINT UQ_OrgId UNIQUE (OrgId)
);
CREATE INDEX IX_Employees_OrgId ON dbo.Employees (OrgId);  -- 支援範圍查詢

插入節點（手動指定路徑）

INSERT INTO dbo.Employees VALUES ('/',           10000, N'陳希章', N'CEO');
INSERT INTO dbo.Employees VALUES ('/1/',         10001, N'張三',   N'CTO');
INSERT INTO dbo.Employees VALUES ('/2/',         10002, N'李四',   N'CFO');
INSERT INTO dbo.Employees VALUES ('/1/1/',       10003, N'王五',   N'IT Manager');
INSERT INTO dbo.Employees VALUES ('/1/2/',       10004, N'趙六',   N'Manager');
INSERT INTO dbo.Employees VALUES ('/1/1/1/',     10005, N'洪七',   N'Employee');

核心查詢範例

1. 取得節點層級

SELECT *, OrgId.GetLevel() AS Lvl FROM dbo.Employees;

2. 查詢某節點的所有子孫（含自身）

DECLARE @Boss HIERARCHYID = (SELECT OrgId FROM dbo.Employees WHERE EmployeeId = 10000);
SELECT *, OrgId.GetLevel() AS Lvl
FROM dbo.Employees
WHERE OrgId.IsDescendantOf(@Boss) = 1;

3. 查詢某節點的所有祖先（含自身）

DECLARE @Emp HIERARCHYID = (SELECT OrgId FROM dbo.Employees WHERE EmployeeId = 10004);
SELECT *, OrgId.GetLevel() AS Lvl
FROM dbo.Employees
WHERE @Emp.IsDescendantOf(OrgId) = 1;

4. 取得直接父節點

SELECT @Emp.GetAncestor(1) AS ParentOrgId;

5. 取得根節點

SELECT @Emp.GetRoot() AS RootOrgId;

6. 在現有子節點間插入新子節點

-- 在 /1/1/ (IT Manager) 下新增子節點，置於 /1/1/1/ (洪七) 之後
DECLARE @Parent HIERARCHYID = (SELECT OrgId FROM dbo.Employees WHERE EmployeeId = 10003);
DECLARE @LastChild HIERARCHYID = (SELECT MAX(OrgId) FROM dbo.Employees WHERE OrgId.GetAncestor(1) = @Parent);
DECLARE @NewOrgId HIERARCHYID = @Parent.GetDescendant(@LastChild, NULL);  -- NULL 表示附加在最後
INSERT INTO dbo.Employees VALUES (@NewOrgId, 10006, N'新員工', N'Junior');

7. 移動子樹（Re-parenting）

-- 將趙六 (10004) 及其下屬從 CTO 移至 CFO
DECLARE @OldParent HIERARCHYID = (SELECT OrgId FROM dbo.Employees WHERE EmployeeId = 10001); -- CTO
DECLARE @NewParent HIERARCHYID = (SELECT OrgId FROM dbo.Employees WHERE EmployeeId = 10002); -- CFO
DECLARE @SubtreeRoot HIERARCHYID = (SELECT OrgId FROM dbo.Employees WHERE EmployeeId = 10004);

UPDATE dbo.Employees
SET OrgId = OrgId.GetReparentedValue(@OldParent, @NewParent)
WHERE OrgId.IsDescendantOf(@SubtreeRoot) = 1;

方法速查表

效能與索引建議

• 在 hierarchyid 欄位建立一般 B-tree 索引即可支援 IsDescendantOf、GetAncestor 等範圍查詢。
• 深度超過 1000 層或極大寬度樹狀結構，考慮補充 lft/rgt 巢狀集合欄位輔助報表查詢。
• hierarchyid 不支援 FILESTREAM、COLUMNSTORE 索引。

遷移自傳統遞迴模型

若現有資料表採 ParentId 遞迴外鍵，可用遞迴 CTE 產生 hierarchyid 路徑並回寫：

WITH cte AS (
    SELECT EmployeeId, CAST('/' AS HIERARCHYID) AS OrgId, 0 AS Lvl
    FROM dbo.Employees WHERE ParentId IS NULL
    UNION ALL
    SELECT e.EmployeeId,
           CAST(p.OrgId.ToString() + CAST(ROW_NUMBER() OVER (PARTITION BY p.EmployeeId ORDER BY e.EmployeeId) AS VARCHAR(10)) + '/' AS HIERARCHYID),
           p.Lvl + 1
    FROM dbo.Employees e JOIN cte p ON e.ParentId = p.EmployeeId
)
UPDATE e SET OrgId = c.OrgId FROM dbo.Employees e JOIN cte c ON e.EmployeeId = c.EmployeeId;

hierarchyid 讓階層式資料操作從「寫遞迴」變成「呼叫方法」，大幅降低開發複雜度與查詢成本，是 SQL Server 處理樹狀結構的首選工具。