To demonstrate using the usp_Reparent procedure,
run the code in Listing 3. The code first queries the
Employees table, producing the output shown in Table 1.
The code then invokes the usp_Reparent procedure to
reparent the subtree of employee 5 (Jiru) under manager
9 (Rita). Finally, the code queries the Employees table again to present the hierarchy after the subtree
has been moved. Table 2 shows the output of this
query.
Converting Parent-Child to
HIERARCHYID
Suppose you have an existing hierarchy represented as
an adjacency list with parent-child relationships, and
you need to convert it to a representation based on the
new HIERARCHYID data type. To achieve this task,
first run the code in Listing 4 and Listing 5.
The code in Listing 4 creates and populates the source
EmployeesOld table, where the parent-child relationships
are reflected by the mgrid-empid attributes. The
code in Listing 5 creates the target EmployeesNew table,
where the converted hierarchy of employees will be
stored. The hid attribute will hold the HIERARCHYID
value for each employee.
To perform the conversion, I take the following steps:
1. Define a regular common table expression (CTE— call it EmpsRN) that calculates a row number for
each employee; the row number is partitioned by
the manager (mgrid) and ordered by the attributes
that you want to determine order among siblings
(say, empid).
2. Define a recursive CTE (call it EmpPaths) that
queries EmpsRN and builds a character path for
each employee. The anchor member will query the
root employee (CEO), and assign ‘/’ as the path.
The recursive member will query the direct subordinates
of the previous level of managers in each
iteration, then concatenate to the manager’s path
the current employee’s row number and a slash.
3. Query the EmpPaths table and convert the path
that was produced for each employee to the
HIERARCHYID data type, and insert the result
rows to the EmployeesNew table.
The complete solution that converts the old hierarchy
to the new one is shown in Listing 6. After running the code in Listing 6, query the new
hierarchy as follows:
SELECT REPLICATE(‘ | ‘, lvl) + empname AS
empname,hid.ToString() AS path
FROM dbo.EmployeesNew
ORDER BY hid;
You’ll get the output shown in Table 3, where you can
see the logical paths that were originally constructed
from the row numbers of the employees in the management
chain leading to each employee.
Practice, Practice, Practice
The new HIERARCHYID data type provides a native
way to handle hierarchies, with behavior and functionality
encapsulated in the type and exposed in the form
of methods. The purpose of having a native type is to
simplify maintaining and querying hierarchies, but as is
typical with new tools, the HIERARCHYID data type
requires some getting used to. Requests for a subgraph,
path, presentation, reparenting, and so on simply look
different when the hierarchy is represented with the
HIERARCHYID data type. Only after you spend
enough time practicing with a new tool can you truly
appreciate whether it improves your experience.