锋利的SQL2014：层次结构操作之Hierarchyid(三)

UPDATE dbo.Employees

SET Org_Node =Org_Node.GetReparentedValue(@oldRoot, @newRoot)

WHEREOrg_Node.IsDescendantOf(@oldRoot) = 1;

执行下面的语句，查询结果如表19-33所示。

SELECT *,Org_Node.ToString() AS Org_Node_Str

FROM dbo.Employees;

表19-33 将部门经理A移动到副总经理B下面

可以看到，部门经理A的节点变成了“/2/1/”，成功地变更到副总经理B下面。但是，这里存在一个问题，就是部门经理A与部门经理C的hierarchyid是相同的，出现了重复值，hierarchyid数据类型虽然用于表示层次结构，但它并不强制实现层次结构，层次的准确性由用户自己掌控。当然，可以通过为hierarchyid类型列建立唯一索引的方式避免出现重复条目。当然，出现此问题的原因是我们没有判断副总经理B下面层级中的最大hierarchyid值，错误地指定了一个已存在hierarchyid。下面来看一下正确的解决方法。

首先使用下面的语句恢复数据的原状。

UPDATE dbo.Employees

SET Org_Node = '/1/1/'

WHERE EmployeeId = 4;

UPDATE dbo.Employees

SET Org_Node = '/1/1/1/'

WHERE EmployeeId = 6;

下面的语句创建了一个存储过程，可以实现节点的变更。该过程接受两个参数，一个是需要调整雇员的D，另一个是其目标管理人员的ID。注意第三条SELECT语句中MAX函数和GetAncestor方法的使用，该语句的作用是在目标管理人员下一层级最大hierarchyid值的基础上获得一个新的hierarchyid值。

CREATE PROCEDUREMoveOrg(@EmpID int, @newMgrID int )

AS

BEGIN

DECLARE @nold hierarchyid, @nnew hierarchyid

SELECT @nold = Org_Node FROM dbo.EmployeesWHERE EmployeeId = @EmpID ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

SELECT @nnew = Org_Node FROM dbo.EmployeesWHERE EmployeeId = @newMgrID ;

SELECT @nnew = @nnew.GetDescendant(MAX(Org_Node), NULL)

FROM dbo.Employees WHERE Org_Node.GetAncestor(1)=@nnew ;

UPDATE dbo.Employees

SET Org_Node =Org_Node.GetReparentedValue(@nold, @nnew)

WHERE Org_Node.IsDescendantOf(@nold) = 1 ;

COMMIT TRANSACTION

END ;

执行存储过程，可以看到部门经理A成功地调整到了副总经理B下面，并且部门经理A的hierarchyid值是在层级最大值“/2/1/”的基础上递增获得“/2/2/”。查询结果如表19-34所示。

EXECUTE dbo.MoveOrg 4, 3; --执行存储过程

SELECT *,Org_Node.ToString() AS Org_Node_Str

FROM dbo.Employees;

表19-34 使用存储过程将部门经理A移动到副总经理B下面

19.8.5 hierarchyid的索引策略

用于对分层数据进行索引的策略有两种：深度优先和广度优先。深度优先索引，子树中各行的存储位置相邻，简而言之，就是以hierarchyid值排序的方式存储，如图19-2所示。对于本节的示例而言，就是经理管理的所有雇员都存储在其经理的记录附近。

图19-2 深度优先索引策略

下面的语句基于Org_Node列，创建了深度优先索引。

CREATE UNIQUE INDEXOrgNode_Depth_First

ON dbo.Employees(Org_Node);

下面的查询语句按Org_Node排序输出，注意其中的Org_Node_Str列，这就是深度优先的索引存储方式。如表19-35所示。<??http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vcD48cD5TRUxFQ1QgKixPcmdfTm9kZS5Ub1N0cmluZygpIEFTIE9yZ19Ob2RlX1N0ciA8L3A+PHA+RlJPTSBkYm8uRW1wbG95ZWVzIDwvcD48cD5PUkRFUiBCWSBPcmdfTm9kZTs8L3A+PHA+se0xOS0zNSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgye62yNPFz8i1xMv30v205rSit73KvTwvcD48dGFibGUgYm9yZGVyPQ=="1" cellspacing="0" cellpadding="0" width="100%">

Org_Node

EmployeeId

Title

Org_Level

Org_Node_Str

0x

1

总经理

0

/

0x58

2

副总经理A

1

/1/

0x5B40

5

部门经理B

2

/1/2/

0x5B56

7

员工B

3

/1/2/1/

0x5B58B0

10

员工D

3

/1/2/1.1/

0x5B5A

8

员工C

3

/1/2/2/

0x68

3

副总经理B

1

/2/

0x6AC0

9

部门经理C

2

/2/1/

0x6B40

4

部门经理A

2

/2/2/

0x6B56

6

员工A

3

/2/2/1/

广度优先索引，是将层次结构中每个级别的各行存储在一起，简而言之，就是按层级排序的方式存储，如图19-3所示。对于本节的示例而言，同一经理直属的各雇员的记录存储在相邻位置。

图19-3 广度优先索引策略

下面的语句基于Org_Node列，创建了广度优先索引。

CREATE CLUSTERED INDEXOrgNode_Breadth_First

ONdbo.Employees(Org_Level);

下面的查询语句按Org_Level排序输出，注意其中的Org_Level列，这就是广度优先的索引存储方式。如表19-36所示。

SELECT *,Org_Node.ToString() AS Org_Node_Str

FROM dbo.Employees

ORDER BY Org_Level;

表19-36 广度优先的索引存储方式