SQL Server提供了一个新的数据类型 HierarchyID，用来处理层次结构的数据，这个数据类型是系统内置的CLR数据类型，不需要专门激活 SQL/CLR 功能即可使用。当需要表示各值之间的嵌套关系，并且该关系可以采用顺序路径语法表达时，HierarchyID 非常有用。
顺序路径看起来有点像文件路径，但它不使用目录和文件名，而是使用数值。与其他父/子关系类似，所有顺序路径都必须归结于根节点。在 SQL Server 2008 中，根节点的文本表示是一个 (/) 字符。具有顺序路径的元素通常由整数表示，但也可能使用小数。顺序路径必须使用另一个 (/) 字符结束。但顺序路径并不以文本形式存储在数据库中。而是将其采用数学方法散列成二进制值，然后将这些二进制值存储在数据页中。
示例代码如下，Parentid作为计算列，从ID中获取 Parent ID。
create table dbo.emph2
(
idpath hierarchyid not null primary key,
id int not null,
parentid as idpath.GetAncestor(1) persisted foreign key references dbo.emph2(idpath),
descr varchar(100)
)
HierarchyID 数据类型的方法如下，CLR类型区分大小写。
| 方法 | 返回结果 | 参数值 | 用途 |
|---|---|---|---|
| GetAncestor | 代表父级或更高级元素顺序路径的 HierarchyID。 | 整数值，指明沿当前顺序路径向上遍历的级数。 | 找出顺序路径中此实例的父级、祖父级或更高级别的元素。 |
| GetDescendent | HierarchyID，代表当前节点的子节点、孙子节点或更低级别子孙节点的路径。 | 两个 HierarchyID 实例，其中一个可以为 null 或者两者都为 null，用于限制可能返回的子项。 | 获取路径，以便在当前顺序路径中某个深度处插入新元素。 |
| GetLevel | 16 位整数值，代表顺序路径的总深度。 | 无。 | 确定两条顺序路径是否具有相同的深度。 |
| GetRoot | 具有零个元素的顺序路径的 HierarchyID。 | 无。 | 找出任意顺序路径的绝对根。 |
| IsDescendantOf | 如果作为参数传入的顺序路径是调用实例的子项，则返回 1。 | HierarchyID 实例。 | 确定给定的 HierarchyID 是否是另一个实例的子项。 |
| Parse | HierarchyID 实例。 | 顺序路径的文本表示。 | 从给定的路径创建 HierarchyID 实例。当 HierarchyID 实例设置为字符串时将隐式调用此方法。 |
| GetReparentedValue | 在将当前项目从一条路径移动到另一条路径时，代表完整顺序路径的 HierarchyID。 | 当前顺序路径表示为 HierarchyID，且目标顺序路径也表示为 HierarchyID。 | 将一行或多行值从一条父顺序路径移动到另一条父顺序路径。 |
| ToString | HierarchyID 顺序路径的文本表示。 | 无。 | 分析 HierarchyID 的顺序路径。 |
GetRoot是个静态方法，在Server sql 可以通过类型直接调用，Root 其实是一个字符 '\'，HierarchyID 类型的数据无法直接print，必须调用ToString()方法，才能显示出来。
print HierarchyID::GetRoot().ToString()
示例代码如下
1，插入root元素，root元素是一个‘/’，HierarchyID类型必须有一个Root元素。
--插入root 元素
insert into dbo.emph2(idpath,id,descr)
values(HierarchyID::GetRoot(),0,'root')
2，类型为 HierarchyID 的列不会自动表示树，由应用程序来生成和分配 HierarchyID 值，使行与行之间的所需关系反映在这些值中。
emph表的数据来源于《数据层次结构建模之一》
;with cte(path,id,parentid,descr) as
(
select cast('/'+cast(id as varchar) as varchar(100)) as path, id,parentid,descr
from dbo.emph
where id=1 union all select cast(c.path+'/'+ cast(e.ID as varchar) as varchar(max)) as path,
e.ID,e.ParentID,e.Descr
from dbo.emph e
inner join cte c on e.ParentID=c.id
)
insert into dbo.emph2(idpath,id,descr)
select path+'/',id,descr
from cte
3，查看结果
select idpath.ToString(),id,parentid.ToString(),descr
from dbo.emph2
4，查看M1手下的所有员工
select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr
from dbo.emph2 e
inner join dbo.emph2 e2 on e.idpath.IsDescendantOf(e2.idpath)=1
where e2.id=2
5，查看id所在的Level，只需要调用HierarchyID类型的GetLevel()。
select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr,e.idpath.GetLevel() as Level
from dbo.emph2 e
inner join dbo.emph2 e2 on e.idpath.IsDescendantOf(e2.idpath)=1
where e2.id=2
