使用 SQL Server 2008 Hierarchyid 建構高效樹狀分類表

傳統遞迴模型痛點

親子關係表（ParentId）查詢子孫節點需遞迴 CTE 或游標，效能隨深度與資料量指數級下降。

Path 冗欄位優化

新增 Path 欄位（如 /1/5/12/），配合 LIKE 'path%' 左匹配 + 索引，查詢效能大幅提升，但維護成本較高（移動節點需更新整棵子樹）。

Hierarchyid 原生型別優勢

• 內建 GetDescendant()、GetAncestor()、IsDescendantOf() 等方法。
• 儲存緊湊（變長二進位），支援索引（深度優先/廣度優先）。
• 移動子樹只需更新根節點 hierarchyid，子孫自動推算新路徑。

實作範例：客戶地區層級

CREATE TABLE Customer (
    CustId INT IDENTITY PRIMARY KEY,
    CustName NVARCHAR(50),
    RegionPath HIERARCHYID,
    RegionLevel AS RegionPath.GetLevel() PERSISTED
);

-- 根節點
INSERT INTO Customer (CustName, RegionPath) VALUES ('Root', hierarchyid::GetRoot());

-- 子節點：GetDescendant(NULL, NULL) 取中間值
DECLARE @parent HIERARCHYID = hierarchyid::GetRoot();
INSERT INTO Customer (CustName, RegionPath)
VALUES ('North', @parent.GetDescendant(NULL, NULL));

-- 查詢某節點所有後代
DECLARE @node HIERARCHYID = (SELECT RegionPath FROM Customer WHERE CustName = 'North');
SELECT * FROM Customer WHERE RegionPath.IsDescendantOf(@node) = 1;

索引策略

• 深度優先索引：CREATE CLUSTERED INDEX idx_dfs ON Customer(RegionPath); 適合子樹查詢。
• 廣度優先索引：需計算層級輔助欄位，適合同層級查詢。