在数据库设计中，经常需要处理具有层级关系的数据。例如，一个公司的员工结构就是一个典型的层级结构，其中每个员工可能有一个或多个下属。为了在数据库中有效地表示这种层级关系，可以使用HierarchyID数据类型。HierarchyID是一种特殊的数据类型，它允许在表中构建数据元素之间的关系，特别是用来表示树形结构中的位置。
在开始使用HierarchyID之前，了解它的一些基本特性是非常重要的。HierarchyID是一个新的系统CLR类型，它支持树形结构。它内部以varbinary格式存储，最大长度不超过900字节。HierarchyID存储的路径提供了树的拓扑排序。此外，它还提供了一组方法来提供树形结构的功能，包括获取祖先、获取后代、获取层级、获取根节点、判断是否为后代、解析字符串、读取、重新设置父节点、转换为字符串等。
HierarchyID还支持高效的查询，可以利用深度优先和广度优先索引。以下是一些提供的方法：
以下是一个SQL代码示例，展示了如何创建一个使用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作为数据类型。
以下是一个SQL存储过程的示例，展示了如何将数据插入到使用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作为数据类型之一的列。
以下是一些SQL查询示例，展示了如何查询包含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;