Web Table 2 shows the output of this query, and Web
Figure 1 shows the execution plan. Notice in the plan
that the depth-first index created on the hid column
is used here efficiently, preventing the need for a sort
operation.
To return a manager and all his or her subordinates
in all levels, you can use the IsDescendantOf method.
This method is invoked for a given HIERARCHYID
value, and accepts a HIERARCHYID value as input.
This method returns 1 if the input employee is a
descendant of the given employee, and 0 if it isn’t. As
an example, the following query returns Ina (employee
3) and all of her subordinates in all levels:
SELECT C.empid, C.empname, C.lvl
FROM dbo.Employees AS P
JOIN dbo.Employees AS C
ON P.empid = 3
AND C.hid.IsDescendantOf(P.hid) = 1;
The query joins two instances of the Employees table,
one called P, representing the parent/manager, and
the other called C, representing children/subordinates.
The ON clause filters only the row for employee 3
from the P instance, and using the IsDescendantOf
method, filters all subordinates of employee 3 from the
C instance. Web Table 3 shows the output of this query,
and Web Figure 2 shows the execution plan.
Notice in the plan that the depth-first index
created on the hid column is used here efficiently,
scanning the consecutive range of rows at the leaf
with Ina (employee 3) and all her subordinates in all
levels. The range appears in the plan as >= P.hid and
<= Expr1004. If you inspect the Compute Scalar
operator that calculates Expr1004, you will find that
it represents P.hid.DescendantLimit(), which is the
maximum possible value under P.hid.
You can limit the number of levels of subordinates
to return below the given manager by filtering the level
difference between the subordinate and manager. For
example, the following query returns Ina and all of her
subordinates up to two levels below her:
SELECT C.empid, C.empname, C.lvl
FROM dbo.Employees AS P
JOIN dbo.Employees AS C
ON P.empid = 3
AND C.hid.IsDescendantOf(P.hid) = 1
WHERE C.lvl - P.lvl <= 2;
To return all managers of a given employee in all levels,
you need to make minor revisions to the query that
returns a manager and all subordinates in all levels.
Simply filter only the given employee from the C
instance (subordinates) instead of the P instance (managers),
and return the attributes from the P instance.
For example, this query returns Didi (employee 14) and
all her managers in all levels:
SELECT P.empid, P.empname, P.lvl
FROM dbo.Employees AS P
JOIN dbo.Employees AS C
ON C.empid = 14
AND C.hid.IsDescendantOf(P.hid) = 1;
The output of this query (i.e., Didi and her managers)
is shown in Web Table 4.
If you want to limit the number of levels to return,
simply add a filter on the level difference between the
subordinate and the manager. For example, the following
query returns Didi and two levels of managers
above Didi:
SELECT P.empid, P.empname, P.lvl
FROM dbo.Employees AS P
JOIN dbo.Employees AS C
ON C.empid = 14
AND C.hid.IsDescendantOf(P.hid) = 1
WHERE C.lvl - P.lvl <= 2;
To get direct subordinates of a given manager, you
will find the GetAncestor method useful. This method
operates on a HIERARCHYID value (call it v), and
accepts an integer (call it l) as input. This method
returns the HIERARCHYID value of the ancestor
who is one level above v. As an example, the following
query shows how to return all direct subordinates of
Eitan (employee 2):
SELECT C.empid, C.empname
FROM dbo.Employees AS P
JOIN dbo.Employees AS C
ON P.empid = 2
AND C.hid.GetAncestor(1) = P.hid;
This query filters only the row for employee 2 from the
instance P, and returns all employees from the instance
C for whom the manager one level above is employee 2.
The output for this query is shown in Web Table 5, and
the execution plan is shown in Web Figure 3. Notice
in the plan that the breadth-first index created on lvl
and hid is used here.
Similarly, if you want to return all subordinates of
Eitan, two levels below, simply specify 2 as the input to
the GetAncestor function:
SELECT C.empid, C.empname
FROM dbo.Employees AS P
JOIN dbo.Employees AS C
ON P.empid = 2 AND C.hid.GetAncestor(2) = P.hid;
More To Come
You can use SQL Server 2008’s new HIERARCHYID
data type to maintain and query hierarchical data. In
this article I discussed performance aspects of the data
type and explained how different indexing strategies
can support different types of requests. Next month
I’ll explain how you can reparent nodes, move complete
subtrees, and convert a traditional parent-child
representation of a hierarchy to one that uses the new
HIERARCHYID data type.
End of Article
Prev. page
1
[2]
next page -->
