SQL Server 2008 中的 HierarchyID 数据类型

引言

SQL Server 2008 的一项出色的新功能是 HierarchyID 数据类型。

HierarchyID 数据类型允许你在表中的数据元素之间建立关系，特别是为了表示层次结构中的位置。

关于 HierarchyId 数据类型的事实

在开始使用 HierarchyID 之前，你需要了解以下几点：

1. 新的系统 CLR 类型，支持树结构。
2. 内部存储为 varbinary，长度小于等于 900 字节。
3. 保存一个路径，该路径提供树的拓扑排序。
4. 拥有一组提供树功能的内置方法：GetAncestor、GetDescendant、GetLevel、GetRoot、IsDescendant、Parse、Read、Reparent、ToString、Write。
5. 高效的查询，可以利用深度优先和广度优先索引。

提供的内置方法如下

• GetAncestor：返回一个 HierarchyID，它代表此 HierarchyID 节点的第 n 个祖先。
• GetDescendant：返回此 HierarchyID 节点的子节点。
• GetLevel：返回一个整数，表示此 HierarchyID 节点在整个层次结构中的深度。
• GetRoot：返回此层次结构树的根 HierarchyID 节点。静态方法。
• IsDescendant：如果传入的子节点是此 HierarchyID 节点的后代，则返回 true。
• Parse：将层次结构的字符串表示形式转换为 HierarchyID 值。静态方法。
• Reparent：将层次结构中的一个节点移动到层次结构中的新位置。
• ToString：返回一个字符串，其中包含此 HierarchyID 的逻辑表示形式。

示例

CREATE TABLE dbo.Employees
(
  empid   INT NOT NULL,
  hid     HIERARCHYID NOT NULL,
  lvl AS hid.GetLevel() PERSISTED,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX idx_depth_first
  ON dbo.Employees(hid);
CREATE UNIQUE INDEX idx_breadth_first
  ON dbo.Employees(lvl, hid);
CREATE UNIQUE INDEX idx_empid
  ON dbo.Employees(empid);

在上面的代码片段中，我使用 HierarchyID 作为数据类型创建了一个表。

CREATE PROC dbo.usp_AddEmp 
  @empid AS INT, @mgrid AS INT = NULL,
  @empname AS VARCHAR(25), @salary AS MONEY
AS

DECLARE @hid AS HIERARCHYID, @mgr_hid AS HIERARCHYID,
  @last_child_hid AS HIERARCHYID;

IF @mgrid IS NULL
  SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
  SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
  SET @last_child_hid =
    (SELECT MAX(hid) FROM dbo.Employees
     WHERE hid.GetAncestor(1) = @mgr_hid);
  SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);
END

INSERT INTO dbo.Employees(empid, hid, empname, salary)
  VALUES(@empid, @hid, @empname, @salary);

在上面的代码片段中，我创建了一个存储过程，可用于将数据插入到包含 HierarchyID 作为数据类型的一个列的表中。

-- Subtree
SELECT C.*
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON P.empid = 3
    AND P.hid.IsDescendant(C.hid) = 1;

-- Path
SELECT P.*
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON C.empid = 14
    AND P.hid.IsDescendant(C.hid) = 1;

-- Presentation/sorting
SELECT
  REPLICATE(' | ', lvl) + empname AS empname, hid.ToString() AS path
FROM dbo.Employees
ORDER BY hid;

上面的代码片段显示了如何查询包含 HierarchyID 列的表。

结论

因此，现在有了 HierarchyID 数据类型，我们可以使用更结构化的代码生成具有父子关系树。

希望您喜欢这篇文章。祝您编程愉快！！！