| Executive Summary: SQL Server 2008’s new data type, HIERARCHYID, lets you represent nodes in a hierarchy and query the hierarchy, as well as move a subtree from one parent location in the hierarchy to another with the GetReparentedValue method, and convert a parent-child representation of the hierarchy to a representation that uses the HIERARCHYID data type. |
SQL Server 2008’s new data type,
called HIERARCHYID, provides
powerful new capabilities.
For example, you can use HIERARCHYID to
represent nodes in a hierarchy and to query the hierarchy,
as I demonstrated in last month’s column. (See
“HierarchyID,” July 2008, InstantDoc ID 99036.)
Another capability you can use is to move a subtree
from one parent location in the hierarchy to another
by using the GetReparentedValue method. In addition,
you can convert a parent-child representation
of the hierarchy to a representation that uses the new
HIERARCHYID data type. I’ll walk through these
capabilities in this month’s column, using the same
Employees table that I used in last month’s column. If
you’re following along, you’ll want to run the code in
Listing 1 to create the Employees table and
populate it with sample data.
Moving a Subtree
When I showed how to insert new nodes into the hierarchy
last month, I explained how to use the GetDescendant
method to calculate a HIERARCHYID value for
a new node under an existing node. You might need to
handle other types of changes within hierarchies as well.
For example, you might need to move a whole subtree of
employees from one manager to another (i.e., move an
employee and all of his or her subordinates).
To achieve this task you can use the GetReparented-
Value method of the HIERARCHYID data type. You
invoke this method on the HIERARCHYID value
of the node you want to reparent (call it @existing_
node_hid), and provide as inputs the value of the old
parent (call it @old_parent_hid) and the value of the
new parent (@new_mgr_hid). Here’s the syntax of the
GetReparentedValue method:
@existing_node_hid.GetReparentedValue
(@old_parent_hid,@new_parent_hid)
Note that this method doesn’t overwrite or change the
value in @existing_node_hid. Instead, it returns a new
HIERARCHYID value. You must update the target
node’s HIERARCHYID value with the new one. Logically,
the GetReparentedValue method does something
very simple—it substitutes the part of the existing node’s
path that represents the old parent’s path with the new
parent’s path. If the path of the existing node is /x/a/,
the path of the old parent is /x/, and the path of the new
parent is /y/, the GetReparentedValue method would
return /y/a/. For example, if the path of the existing node
is /1/2/1/, the path of the old parent is /1/2/, and the path
of the new parent is /2/1/1/4/, the GetReparentedValue
method would return /2/1/1/4/1/.
If the target parent already has children, the Get-
ReparentedValue method won’t necessarily produce
a unique value. If you reparent node /x/a/ from old
parent /x/ to new parent /y/, and /y/ already has a child
/y/a/, you will get a duplicate value. The trick is that
you don’t use the GetReparentedValue method to
move a single node from one parent to another; for this
task you can simply use the GetDescendant
method, and produce a completely new value.
You use the GetReparentedValue method
when you need to move a whole subtree in conjunction
with the GetDescendant method.
Suppose you need to move the whole subtree of
employee /x/a/ from old parent /x/ to new parent /y/
that already has children. You achieve this with the
following steps:
1. Use the GetDescendant method to produce a
completely new HIERARCHYID value under /y/
(call it /y/b/).
2. Update the HIERARCHYID value of all nodes
in the subtree of /x/a/ (including self), to the return
value of node.GetReparentedValue(/x/a/, /y/b/).
Because /y/b/ is a completely new HIERARCHYID
value under the target parent, /y/b/ has no existing children; hence, moving the subtree doesn’t cause conflicting
HIERARCHYID values.
Run the code in Listing 2 to create the usp_Reparent stored procedure that implements this logic
to move a subtree. The code in the procedure first
opens a transaction. Within the transaction, the code
retrieves the hid value of the new manager (@new_mgr_hid) and specifies the UPDLOCK hint to obtain
an UPDATE lock on the row. Only one transaction
can hold an UPDATE lock at a time; once obtained,
this lock is kept until the end of the transaction. This
way, if simultaneous invocations of usp_Reparent
and usp_AddEmp make requests to create a new
HIERARCHYID value under the same target manager,
those requests won’t produce duplicate values, but
instead will be queued.
The code retrieves the hid value of the old manager
(@old_root). Next, the code uses the GetDescendant
method to produce a new HIERARCHYID
value under the new manager (@new_root). Then,
the GetReparentedValue method updates the
HIERARCHYID values of the employee that is moved
and of all descendants of that employee. Finally, the
code commits the transaction, releasing all locks.
Continued on page 2