五餅二魚工作室

基本上從開始教 SQL Server 以來，如何利用 SQL 來做 BOM (Bill of Materials ) 的展階，都是很容易被詢問到的問題，這一類的處理通常我們都只能使用暫存資料表或多層的 SELF JOIN 來進行，而當到了 SQL Server 2005 之後，SQL Server 加入了 CTE 的語法支援，此時這類的階層式處理就很習慣式的換成 CTE 來進行。然而在 SQL Server 2008 之後，SQL Server 增加了一個新的資料型別 hierarchyid 專門用來處理這類型的階層式資料，但是因為一來使用 CTE 就已經很習慣了，再加上當時都是使用 Delphi 在做開發，就沒有特別使用這種只有在 SQL Server 內專門使用的資料型別來做處理。

前一陣子換了新工作，負責維護的產品內有大量使用 hierarchyid 的資料型別，因此就趁著放假期間，整理一下相關使用的經驗，這樣讓後續維護的同仁也就不用那麼辛苦了。

首先甚麼是「hierarchyid」呢 ? hierarchyid 是 SQL Server 所提供的特殊資料型別，用於表示樹狀結構。它以 binary 格式儲存資料，並支援一些內建函數，能有效支援節點插入、查詢、重構等操作。以下我們用一個範例來做說明，也順便講一下我剛開始學的時候所踩的一些地雷。

這裡我先建立一個範例資料表，這個資料表中比較特別的有兩個欄位 PartNode 和  HierarchyPath，其中 PartNode 用來存放「階層式關聯性」，而不是父階層的節點。且在這裡因為型別 hierarchyid 是放在 DDL 的指令裡面，所以在這裡可以不區分大小寫，但是後續在 SQL 語法中要特別注意，一定要使用小寫，否則會告訴您不是定義的系統類型；而 HierarchyPath 則是一個計算欄位，在這個範例中主要來顯示較容易看得懂的階層，但實際上並不一定需要這個欄位。

-- 
CREATE TABLE BOMDemo (
    PartID INT PRIMARY KEY,
    PartName NVARCHAR(100),
    PartNode HIERARCHYID,
    HierarchyPath AS PartNode.ToString() PERSISTED
);

為了簡化新增資料的流程，我們也可以建立一個儲存程序來自動根據父節點加入新的子節點：

CREATE OR ALTER PROCEDURE [dbo].[AddPart]
    @ParentPartID INT,             -- 指定父節點的 PartID
    @NewPartID INT,                -- 新增子節點的 PartID（須唯一）
    @NewPartName NVARCHAR(100)    -- 新增子節點的名稱
AS
BEGIN
    DECLARE @ParentNode HIERARCHYID;      -- 父節點的 hierarchyid 值
    DECLARE @LastChild HIERARCHYID;       -- 該父節點下目前最後一個子節點的 hierarchyid

    -- 取得父節點的 hierarchyid
    SELECT @ParentNode = PartNode
    FROM BOMDemo
    WHERE PartID = @ParentPartID;

    -- 使用可序列化交易確保在多用戶插入時不會產生衝突
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;

    -- 找出目前此父節點下最大的子節點（代表最後一個子節點）
    SELECT @LastChild = MAX(PartNode)
    FROM BOMDemo
    WHERE PartNode.GetAncestor(1) = @ParentNode;

    -- 插入新的子節點，使用 GetDescendant 產生新的層級編碼
    INSERT INTO BOMDemo (PartID, PartName, PartNode)
    VALUES (
        @NewPartID,
        @NewPartName,
        @ParentNode.GetDescendant(@LastChild, NULL)
    );

    COMMIT;
END;
GO

這樣我們就可以方便使用這個 SP 來新增範例資料了

-- 插入根節點（筆電）
INSERT INTO BOMDemo (PartID, PartName, PartNode)
VALUES (1, 'Laptop', hierarchyid::GetRoot());

-- 第一層
EXEC AddPart 1, 2, 'Mainboard';
EXEC AddPart 1, 3, 'Screen';
EXEC AddPart 1, 4, 'Battery';

-- 第二層
EXEC AddPart 2, 5, 'CPU';
EXEC AddPart 2, 6, 'RAM';
EXEC AddPart 2, 7, 'Power IC';
EXEC AddPart 2, 8, 'IO Controller';
EXEC AddPart 3, 9, 'Panel';
EXEC AddPart 3, 10, 'Touch Module';
EXEC AddPart 3, 11, 'Display Driver';
EXEC AddPart 4, 12, 'Cell';
EXEC AddPart 4, 13, 'Protection Circuit';
EXEC AddPart 4, 14, 'Battery Controller';

這樣我們就可以完成相關範例的資料了

---

除了上述範例的使用之外，hierarchyid 最主要還有提供一些方法來方便階層式的處理

方法	說明
GetRoot()	取得階層的根節點
GetDescendant(child1, child2)	產生介於兩節點間的新節點位置
GetAncestor(n)	取得往上 n 層的父節點
GetLevel()	取得當前節點的深度（從 0 開始）
ToString()	將 hierarchyid 轉換為可讀字串
Parse()	將字串轉為 hierarchyid 型別
IsDescendantOf()	判斷某節點是否為另一節點的子代

因此針對這些方法我們做個範例

-- 找出根結點
SELECT PartID, PartName FROM BOMDemo
WHERE PartNode = hierarchyid::GetRoot();

-- 查詢某節點的所有後代
SELECT PartID, PartName FROM BOMDemo
WHERE PartNode.IsDescendantOf((SELECT PartNode FROM BOMDemo WHERE PartName = 'Mainboard')) = 1;

-- 查詢某節點的直接父節點
SELECT t1.PartID, t1.PartName, t2.PartID ParentID, t2.PartName ParentName
FROM BOMDemo t1
JOIN BOMDemo t2 ON t1.PartNode.GetAncestor(1) = t2.PartNode
WHERE t1.PartName = 'RAM';

-- 取得某節點的階層層級
SELECT PartName, PartNode.GetLevel() AS Level FROM BOMDemo;

---

所以從上述範例中看起來，如果採用 hierarchyid 來處理階層式資料的，對於需要較少維護但需要大量查詢階層的情境中，似乎會比利用 CTE 來處理相對方便得多，但如果樹狀結構時常需要更新的時候，假設某一個節點要更換父節點的情況下，則所有該節點以下的所有節點都需要更新，這一段就相對比較麻煩，這裡我們會寫一個範例還示範將 BOM 的某個節點移到新的父節點 , 這裡我們將處理封裝成為一個 SP

CREATE OR ALTER PROCEDURE [dbo].[MovePart]
    @PartID INT,           -- 要移動的零件 ID
    @NewParentID INT       -- 新的父零件 ID
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @OldNode hierarchyid;
    DECLARE @NewParentNode hierarchyid;
    DECLARE @NewNode hierarchyid;

    -- 1. 取得原始節點和新父節點的 hierarchyid
    SELECT @OldNode = PartNode FROM BOMDemo WHERE PartID = @PartID;
    SELECT @NewParentNode = PartNode FROM BOMDemo WHERE PartID = @NewParentID;

    -- 2. 防呆：不能移到自己或子節點底下
    IF @NewParentNode.IsDescendantOf(@OldNode) = 1 OR @NewParentNode = @OldNode
    BEGIN
        RAISERROR(N'無法將零件移動到自己或自己的子階層之下。', 16, 1);
        RETURN;
    END

    -- 3. 找出在新父節點底下的新位置
    SELECT @NewNode = @NewParentNode.GetDescendant(
        MAX(PartNode), NULL)
    FROM BOMDemo
    WHERE PartNode.GetAncestor(1) = @NewParentNode;

    IF @NewNode IS NULL
        SET @NewNode = @NewParentNode.GetDescendant(NULL, NULL);

    -- 4. 更新要移動的節點本身
    UPDATE BOMDemo
    SET PartNode = @NewNode
    WHERE PartID = @PartID;

    -- 5. 更新所有子節點（包含孫節點等）
    UPDATE BOMDemo
    SET PartNode = PartNode.GetReparentedValue(@OldNode, @NewNode)
    WHERE PartNode.IsDescendantOf(@OldNode) = 1 AND PartID <> @PartID;
END
GO

所以我們如果用上述的 BOM 資料表當範例，我們可以測試一下

EXEC [dbo].[MovePart] 3 , 8

這樣我們重新查新原本的資料表，可以看到原本 PartID 3 的 Screen，我們將他移到 PartID 8 之下，因此他本來的 HierarchyPath 是 /2/ 變成了 / 1 / 4 / 1 ，而旗下所有的子節點也在 SP 內透過 GetReparentedValue 的函數更換新的 PartNode 的屬性

---

基本上就目前使用上看起來，的確在使用 hierarchyid 來做階層式處理，如果有搭配索引的情況下，使用起來效能算是不錯，雖然當節點搬移的時候會比較麻煩一點，但是卻能夠在查詢的時候便利許多，或許在以後針對 BOM、部門組織等階層式的資料，可以改用這樣的方式來做處理。