使用SQL Server 2008中的hierarchyid类型来设计具有树型层次关系的表

本文主要讲述三个内容：
1.如何创建hierarychyid的表，插入数据及基本递归查询。
2.介绍hierarchyid的10种专有函数。
3.介绍hierarchyid特有的深度优先索引(Depth-First Indexing)和广度优先索引(Breadth-First Indexing)

在上一节中

http://blog.csdn.net/tjvictor/archive/2009/07/30/4395677.aspx
我们已经演示了如何在SQL Server中通过主键和外键来存储如下图所示的树型结构数据

虽然通过主键和外键的相互搭配可以满足我们的查询、存储需求，但是这种方式并不易于管理和维护，幸运的是，在SQL Server 2008中提供了一种新的数据类型hierarchyid和相关的操作方法来存储和查询这种树型层次关系数据。

首先创建数据表:
create database TestDb
go
use TestDb
go
Create table EmployeeTreeTable
(
NodeId hierarchyid PRIMARY KEY,
NodeLevel AS NodeId.GetLevel(),
EmployeeId int UNIQUE NOT NULL,
EmployeeName nvarchar(32) NOT NULL,
)
NodeId是记录树型层次的Id，是hierarchyid类型。NodeLevel是个计算列，用于存储当前树是深度值，根节点为0。关于NodeId.GetLevel()方法将在下面章节中详细介绍。

按照上图所示的层次关系为表插入数据：
--插入数据
declare @DepthNode hierarchyid;--深度Id
declare @BreadthNode hierarchyid;--广度Id
--插入根节点
insert into EmployeeTreeTable values(hierarchyid::GetRoot(),1,'项目经理')
--计算深度并插入子节点2
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 1;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),2,'技术经理');
--计算节点2广度，在节点2右边插入节点3
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 2;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),3,'产品经理');
--计算节点3广度，在节点3右边插入节点4
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 3;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),4,'测试经理');
--计算节点2深度并插入子节点5
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 2;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),5,'技术组长1');
--计算节点5广度，在节点5右边插入节点6
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 5;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),6,'技术组长2');
--计算节点4深度并插入子节点7
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 4;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),7,'测试员工1');
--计算节点5深度并插入子节点8
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 5;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),8,'技术员工1');
--计算节点8广度，在节点8右边插入节点9
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 8;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),9,'技术员工2');
--计算节点9广度，在节点9右边插入节点10
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 9;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),10,'技术员工3');
go
select * from EmployeeTreeTable
结果集为：
NodeId NodeLevel EmployeeId EmployeeName
0x 0 1 项目经理
0x58 1 2 技术经理
0x5AC0 2 5 技术组长1
0x5AD6 3 8 技术员工1
0x5ADA 3 9 技术员工2
0x5ADE 3 10 技术员工3
0x5B40 2 6 技术组长2
0x68 1 3 产品经理
0x78 1 4 测试经理
0x7AC0 2 7 测试员工1

1.查询技术组长1所有子节点的员工信息
select * from EmployeeTreeTable
where NodeId.IsDescendantOf(0x5AC0)=1--0x5AC0是技术组长1的NodeId

2.查询技术组长1所有父节点的员工信息
with c as
(
select * from EmployeeTreeTable where EmployeeId = 5
union all
select a.* from EmployeeTreeTable as a
join c on a.NodeId = c.NodeId.GetAncestor(1)
)
select * from c

上面的例子中，使用了很多hierarchyid专有的函数，可能大家还不熟悉，下面我将具体介绍一下hierarchyid的10个函数，分别为：
GetRoot，GetLevel，GetAncestor，GetDescendant，IsDescendantOf，ToString，Parse，GetReparentedValue，Read，Write。
1.GetRoot。返回层次结构树的根节点。注意GetRoot() 是静态方法。
关于SQL中静态方法和实例方法的区别请参见：http://blog.csdn.net/tjvictor/archive/2009/07/29/4390673.aspx
SQL:select * from EmployeeTreeTable where NodeId = hierarchyid::GetRoot()
结果集：
NodeId NodeLevel EmployeeId EmployeeName
0x 0 1 项目经理

2.返回一个表示节点在树中的深度的整数。
前面建表时我们已经使用了这个函数，NodeLevel字段就是用这个函数自动创建的。
SQL：select EmployeeName,NodeId.GetLevel() as TreeLevel from EmployeeTreeTable
结果集为：
EmployeeName TreeLevel
项目经理 0
技术经理 1
技术组长1 2
技术员工1 3
技术员工2 3
技术员工3 3
技术组长2 2
产品经理 1
测试经理 1
测试员工1 2

3.GetAncestor返回表示本节点为的第 n 个父节点的 hierarchyid。
SQL:
declare @NodeId hierarchyid
select @NodeId=NodeId from EmployeeTreeTable where EmployeeId = 5
select EmployeeName,NodeLevel from EmployeeTreeTable where NodeId = @NodeId.GetAncestor(0)
select EmployeeName,NodeLevel from EmployeeTreeTable where NodeId = @NodeId.GetAncestor(1)
select EmployeeName,NodeLevel from EmployeeTreeTable where NodeId = @NodeId.GetAncestor(2)
结果集为：
EmployeeName NodeLevel
技术组长1 2
技术经理 1
项目经理 0
@NodeId.GetAncestor(0) 取自己节点的Id，@NodeId.GetAncestor(1)取父节点的Id,@NodeId.GetAncestor(2)取爷节点的Id，以此类推。

4.GetDescendant返回父级的一个子节点

如果父级为 NULL，则返回 NULL。

如果父级不为 NULL，而 child1 和 child2 为 NULL，则返回父级的子级。

如果父级和 child1 不为 NULL，而 child2 为 NULL，则返回一个大于 child1 的父级的子级。

如果父级和 child2 不为 NULL，而 child1 为 NULL，则返回一个小于 child2 的父级的子级。

如果父级、child1 和 child2 都不为 NULL，则返回一个大于 child1 且小于 child2 的父级的子级。

如果 child1 不为 NULL 且不是父级的子级，则引发异常。

如果 child2 不为 NULL 且不是父级的子级，则引发异常。

如果 child1 >= child2，则引发异常。
我们在插入的SQL语句中已经使用过了这个方法，这里就不再给出SQL示例，请大家参考前面的插入SQL语句。

5.IsDescendantOf如果子节点为本节点的后代，则返回 true
SQL:select * from EmployeeTreeTable where NodeId.IsDescendantOf(0x58)=1
结果集为:
NodeId NodeLevel EmployeeId EmployeeName
0x58 1 2 技术经理
0x5AC0 2 5 技术组长1
0x5AD6 3 8 技术员工1
0x5ADA 3 9 技术员工2
0x5ADE 3 10 技术员工3
0x5B40 2 6 技术组长2

6.ToString返回具有本节点逻辑表示形式的字符串
SQL：select *,NodeId.ToString() as Path from EmployeeTreeTable
结果集为:
NodeId NodeLevel EmployeeId EmployeeName Path
0x 0 1 项目经理 /
0x58 1 2 技术经理 /1/
0x5AC0 2 5 技术组长1 /1/1/
0x5AD6 3 8 技术员工1 /1/1/1/
0x5ADA 3 9 技术员工2 /1/1/2/
0x5ADE 3 10 技术员工3 /1/1/3/
0x5B40 2 6 技术组长2 /1/2/
0x68 1 3 产品经理 /2/
0x78 1 4 测试经理 /3/
0x7AC0 2 7 测试员工1 /3/1/

7.Parse将hierarchyid 的规范字符串表示形式转换为hierarchyid值。即与ToString()函数是相反函数。Parse是静态函数。
SQL：
declare @Path varchar(32) = '/1/2/5/6/'
select hierarchyid::Parse(@Path)
结果集为:0x5B6394

8.GetReparentedValue把当前节点从旧路径更新到新路径
下面的SQL是把技术员工3，从技术组长1节点更新到技术组长2下面。
SQL:
declare @OldNode hierarchyid=0x5AC0;
declare @NewNode hierarchyid=0x5B40;
update EmployeeTreeTable set NodeId = NodeId.GetReparentedValue(@OldNode,@NewNode)
where EmployeeId = 10
结果集中技术员工3的路径从/1/1/3/变成了/1/2/3/。
关于GetReparentedValue的用法比较复杂，我在介绍索引后，会更加详细的说明各种替换情况。

9.Read和Write
Read和Write是供CLS调用的，不能在T-SQL中直接使用。所以这里就不具体介绍两个函数的使用方法了。

hierarchyid有深度优先索引和广度优先索引
当递归查询父子节点时，会利用到深度优先索引；当平行查询兄弟节点时，会利用到广度优先索引。
深度优先索引图：

广度优先索引图：

1.建立深度优先索引:
深度优先索引是hierarchyid默认的索引，只要在hierarchyid列上建立主键，那么就会自动建立hierarchyid索引。

2.建立广度优先索引
广度优先索引必须是个唯一索引且包括NodeLevel和NodeId两列:
CREATE UNIQUE INDEX IX_EmployeeBreadth ON Employee(NodeLevel, NodeId)

需要注意的是采用深度优先、广度优先还是结合使用这两种索引，以及将哪一种设为聚集键（如果有），取决于上述两种查询类型的相对重要性以及 SELECT 与 DML 操作的相对重要性，本文不代表一定要如此建立hierarchyid索引。

最后我们讨论一下hierarchyid的GetReparentedValue几种使用方法。
下面我们先看一个有问题的节点更新:把技术组长1从技术经理更新到产品经理。
SQL:
declare @OldNode hierarchyid=0x58;
declare @NewNode hierarchyid=0x68;
update EmployeeTreeTable set NodeId = NodeId.GetReparentedValue(@OldNode,@NewNode)
where EmployeeId = 5
go
select NodeId.ToString(),* from EmployeeTreeTable
结果集为：
路径 NodeId NodeLevel EmployeeId EmployeeName
/ 0x 0 1 项目经理
/1/ 0x58 1 2 技术经理
/1/1/1/ 0x5AD6 3 8 技术员工1
/1/1/2/ 0x5ADA 3 9 技术员工2
/1/1/3/ 0x5ADE 3 10 技术员工3
/1/2/ 0x5B40 2 6 技术组长2
/2/ 0x68 1 3 产品经理
/2/1/ 0x6AC0 2 5 技术组长1
/3/ 0x78 1 4 测试经理
/3/1/ 0x7AC0 2 7 测试员工1
从结果里面可以看到技术组长已经变成了/2/1，成功更新到产品经理节点下。但是技术组长1下面的子节点技术员工1，2，3却没有相应的更新过来，还是原来的/1/1/1，2，3，但是原先的技术组长1的/1/1节点已经没有了，所以出现了所谓的“断层”现象。
下面提出几种常用更新需求，并且给出相应的SQL实现语句。

1.职位变更。例如技术经理与产品经理职位互换。
针对这种情况，有两种方法。一是把技术经理下面的所有节点Id都更新成产品经理节点下。这种情况变动比较大，不推荐使用。第二种方法是把技术经理的NodeId和产品经理的NodeId互换。下面使用第二种方法：
declare @TechNode hierarchyid=0x58;
declare @ProductNode hierarchyid=0x68;
declare @TempNode hierarchyid=0x59;
update EmployeeTreeTable set NodeId = @TempNode where NodeId = @TechNode;
update EmployeeTreeTable set NodeId = @TechNode where NodeId = @ProductNode;
update EmployeeTreeTable set NodeId = @ProductNode where NodeId = @TempNode;

2.职位升降级。例如技术组长2降级成为技术员工，被挂在技术组长1节点下:
declare @TechTeamLeadNode1 hierarchyid=0x5AC0;
declare @TechEmployeeNode3 hierarchyid=0x5ADE;
update EmployeeTreeTable set NodeId = @TechTeamLeadNode1.GetDescendant(@TechEmployeeNode3,null)
where EmployeeId = 6
部分结果集为：
Path NodeId NodeLevel EmployeeId EmployeeName
/1/1/4/ 0x5AE1 3 6 技术组长2
可见，技术组长2从/1/2变成了/1/1/4

总结：
SQL Server 2008提供的hierarchyid类型使我们能够灵活、方便的操作树型结构。关于hierarchyid还有很多深入的知识，很多灵活的用法，本文不可能一一涉及，这里仅是介绍一些基本用法，抛砖引玉，如果大家在以后的使用中发现什么问题或是更好的解决方案，请联系我。

如需转帐，请注明本文原创自CSDN TJVictor专栏：http://blog.csdn.net/tjvictor/archive/2009/07/30/4395681.aspx