Reordering Nodes within the Hierarchy
Reorganizing the nodes within the tree structure is a common
maintenance task when working with hierarchies. You might need to alter
the tree structure by adjusting the parent-child relationships within
the hierarchy. The hierarchyid type provides a GetReparentedValue
method that makes it easy to handle this kind of maintenance. You will
start by first relocating just a single node without disturbing any
other nodes in the hierarchy. Then you’ll relocate an entire subtree (that is, all of a node’s descendant nodes).
The GetReparentedValue Method
You invoke the GetReparentedValue
method on the node you want to move, passing in two parameters. The
first specifies the original parent (the source), and the second
specifies the new parent (the target). Wanda formerly reported to Amy
but is now reporting to Jill instead (alongside Kevin). You therefore
want to move Wanda’s current position as a child of Amy to be a child
of Jill instead. The following code uses the GetReparentedValue method to perform that change.
DECLARE @EmployeeToMove hierarchyid
DECLARE @OldParent hierarchyid
DECLARE @NewParent hierarchyid
SELECT @EmployeeToMove = NodeId
FROM Employee
WHERE EmployeeId = 389 -- Wanda
SELECT @OldParent = NodeId
FROM Employee
WHERE EmployeeId = 46 -- Amy
SELECT @NewParent = NodeId
FROM Employee
WHERE EmployeeId = 119 -- Jill
-- Wanda now reports to Jill and no longer to Amy
UPDATE Employee
SET NodeId = @EmployeeToMove.GetReparentedValue(@OldParent, @NewParent)
WHERE NodeId = @EmployeeToMove
GO
The hierarchy now looks like Figure 1.
Viewing the results of the following query confirms that Wanda’s NodeIdPath has changed from /1/2/ (child of Amy) to /3/2/ (child of Jill):
SELECT
NodeId,
NodeId.ToString() AS NodeIdPath,
NodeLevel,
dbo.fnGetFullDisplayPath(NodeId) AS NodeIdDisplayPath,
EmployeeName
FROM
Employee
ORDER BY
NodeLevel, NodeId
GO
NodeId NodeIdPath NodeLevel NodeIdDisplayPath EmployeeName
------ ---------- --------- ----------------------------- ------------
0x / 0 Dave Dave
0x58 /1/ 1 Dave > Amy Amy
0x68 /2/ 1 Dave > John John
0x78 /3/ 1 Dave > Jill Jill
0x5AC0 /1/1/ 2 Dave > Amy > Cheryl Cheryl
0x6AC0 /2/1/ 2 Dave > John > Mary Mary
0x7AC0 /3/1/ 2 Dave > Jill > Kevin Kevin
0x7B40 /3/2/ 2 Dave > Jill > Wanda Wanda
0x5AD6 /1/1/1/ 3 Dave > Amy > Cheryl > Richard Richard
0x5ADA /1/1/2/ 3 Dave > Amy > Cheryl > Jeff Jeff
(10 row(s) affected)
Changing the position of a single node with the GetReparentedValue method as you just did does not affect any of that node’s children (since only one row was updated). This means that if Wanda had any child nodes,
they would not move along with Wanda as new descendants of Jill, now
would they move up one level in the hierarchy as direct descendants of
Amy to occupy the position vacated by Wanda. The same would be true if
Wanda were simply deleted rather than moved. Former child nodes of
Wanda would therefore end up having no parent at all, but would rather
have only a grandparent
(which in this case would be Amy, Wanda’s former parent). The result is
a “hole” in your hierarchy. SQL Server will not enforce integrity
checks on the hierarchy to catch this condition, so it’s your job to
handle orphaned
nodes properly. One way to ensure against orphaned nodes is to
transplant an entire subtree at one time. Let’s learn how to do just
that.
You can easily move a larger number of people at one time by
reparenting an entire subtree. Because you’ll be moving all of a node’s
descendants as a single block, no orphaned nodes can result from this
operation. In the next update, you will move all of Amy’s subordinates
to their new manager, Kevin. This is achieved by reparenting all nodes
with hierarchyid values that begin with /1/ (Amy) to /3/1/ (Kevin), except for Amy. You use the IsDescendantOf method to return all of Amy’s descendants for the update. Recall from our earlier discussion of the IsDescendantOf
method that Amy is her own descendant at the 0 level (that is, she’s
the root of her own subtree). You must therefore exclude her (employee
ID 46) from the rows to be updated, as shown in the following code:
DECLARE @OldParent hierarchyid
DECLARE @NewParent hierarchyid
SELECT @OldParent = NodeId
FROM Employee
WHERE EmployeeId = 46 -- Amy
SELECT @NewParent = NodeId
FROM Employee
WHERE EmployeeId = 291 -- Kevin
UPDATE Employee
SET NodeId = NodeId.GetReparentedValue(@OldParent, @NewParent)
WHERE NodeId.IsDescendantOf(@OldParent) = 1
AND EmployeeId <> 46 -- This excludes Amy from the move.
GO
Note
The
employee ID values are hard-coded for Amy and Kevin in this code. This
was done for demonstration purposes. In a real-world implementation,
this logic should be in a stored procedure that accepts any two nodes as input parameters and wraps the SELECT and UPDATE statements in a transaction.
Running this update moves all of Amy’s descendants to be descendants of Kevin, as shown in Figure 2. Notice that the nodes have moved not only across the tree, but down one level as well (since Kevin is one level deeper than Amy).
Selecting all the employees one more time shows how SQL Server updated the hierarchyid values in NodeId
for Cheryl, Richard, and Jeff to reflect their new positions beneath
Kevin. For the first time in this scenario, the hierarchy now runs five
levels deep (counting from 0 to 4), as shown here:
SELECT
NodeId,
NodeId.ToString() AS NodeIdPath,
NodeLevel,
dbo.fnGetFullDisplayPath(NodeId) AS NodeIdDisplayPath,
EmployeeName
FROM
Employee
GO
NodeId NodeIdPath NodeLevel NodeIdDisplayPath EmployeeName
--------- ---------- --------- -------------------------------------- ------------
0x / 0 Dave Dave
0x58 /1/ 1 Dave > Amy Amy
0x68 /2/ 1 Dave > John John
0x78 /3/ 1 Dave > Jill Jill
0x6AC0 /2/1/ 2 Dave > John > Mary Mary
0x7AC0 /3/1/ 2 Dave > Jill > Kevin Kevin
0x7B40 /3/2/ 2 Dave > Jill > Wanda Wanda
0x7AD6 /3/1/1/ 3 Dave > Jill > Kevin > Cheryl Cheryl
0x7AD6B0 /3/1/1/1/ 4 Dave > Jill > Kevin > Cheryl > Richard Richard
0x7AD6D0 /3/1/1/2/ 4 Dave > Jill > Kevin > Cheryl > Jeff Jeff
(10 row(s) affected)
More hierarchyid Methods
Three more hierarchyid methods—Parse, Read, and Write—are provided by the hierarchyid data type, although they are less often used.
Parse is essentially the reverse of ToString. It accepts the same slash-delimited string representation that is returned by ToString and returns the equivalent compacted varbinary hierarchyid value that SQL Server uses internally to represent the nodes. This value could then be passed to ToString, in which case you’d get the original slash-delimited string back. It is the only other static method besides GetRoot, and so it uses the double-colon syntax, as follows:
SELECT hierarchyid::Parse('/2/1/1/') AS NodeId
GO
NodeId
----------
0x6AD6
(1 row(s) affected)
Together, Parse and ToString enable serialization and deserialization to and from hierarchyid binary values and their string representations. Although you could manage the string representations on your own and use Parse to convert them for storage as hierarchyid values, the hierarchyid values are best assigned by calling the GetDescendant method, as we’ve demonstrated throughout our examples.
The last two methods, Read and Write, are the only methods not available in T-SQL, and can be used only in .NET code against the Microsoft.SqlServer.Types.SqlHierarchyId type. These two methods are used to pass hierarchyid values into and out of BinaryReader and BinaryWriter objects. They are also used internally by SQL Server as necessary, such as when reading from or writing to a hierarchyid column or for conversions between varbinary and hierarchyid.
These methods are provided to enable such internal operations, as well
as for integration with your own .NET code running under SQL CLR. They
don’t otherwise serve any real significant function in terms of the
actual implementation of a hierarchical structure.
