[SQL]使用 SQL Server hierarchyid 储存 BOM 阶层资料

对于阶层式的资料，除了利用纪录父阶层的 Id 之外，SQL Server 可以使用其特别专属的 hierarchyid 来做处理 

基本上从开始教 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 来处理相对方便得多，但如果树状结构时常需要更新的时候，假设某一个节点要更换父节点的情况下，则所有该节点以下的所有节点都需要更新，这一段就相对比较麻烦，后续可能再找时间做个範例，来比较使用 CTE 和 hierarchyid 的更换节点的处理好了。