Sql Server 2012 : Hierarchical Data and the Relational Database - Populating the Hierarchy (part 1)

Let’s visualize the organizational chart before you start populating the Employee table with hierarchical data. Figure 1 shows the hierarchy of employees in your fictional organization.

Figure 1. Hierarchical organizational chart.

The first row added to a hierarchical table is always the root node of the tree, and there can be only one root. Additional rows beneath the root are then added as child and grandchild nodes, and so on. Nodes can be rearranged as needed by invoking methods on the hierarchyid values. The tree can grow both broad (with many siblings) and deep (with many descendants).

The GetRoot Method

You begin by inserting your first row for Dave, the marketing manager, at the top of the chart with an employee ID of 6, as shown in Example 1:

Example 1. Inserting the root node row in the hierarchical Employee table.

INSERT INTO Employee
  (NodeId, EmployeeId, EmployeeName, Title)
 VALUES
  (hierarchyid::GetRoot(), 6, 'Dave', 'Marketing Manager') ;
GO

Values can be assigned into hierarchyid columns by invoking methods that return hierarchyid values. When inserting the first row at the top of the chart, you call the GetRoot method on the hierarchyid type itself. The special double-colon (::) syntax is used when calling methods on the data type itself (akin to a static method call in object oriented languages), rather than calling methods on values of the data type (akin to an object instance method call), which uses the normal “dot” syntax . Being the first row in your hierarchical table, the only meaningful hierarchyid value for NodeId is one that specifies the root, and that’s exactly the value returned by hierarchyid::GetRoot().

Also notice that you do not (and cannot) insert a value for NodeLevel, because that is a read-only calculated column that returns the level position of each row. Select the row back out from the table so that you can see what it looks like:

SELECT * FROM Employee
GO

NodeId    NodeLevel EmployeeId  EmployeeName   Title
--------- --------- ----------- -------------- --------------------
0x        0         6           Dave           Marketing Manager

(1 row(s) affected)

The GetDescendant Method

In the preceding output, you see Dave’s record as the root node in the hierarchy at level zero. The hierarchyid value of 0x for NodeId positions Dave as the top-level node, set by the GetRoot method when you inserted the row. Now it’s time to add child nodes for the level-one employees who report directly to Dave. To set the hierarchyid values for these employees, you use the GetDescendant method on Dave’s hierarchyid value. This method generates and returns a hierarchyid value that is a child of the hierarchyid it is invoked on and is a sibling positioned between the two hierarchyid values that get passed in as the method parameters.

Before adding the employees beneath Dave, let’s take a close look at the GetDescendant method. Suppose that you want to construct a generic tree, organized as shown in Figure 1.

Figure 2. Sample hierarchy of nodes.

The code in Example 2 shows how to use GetDescendant to create child and grandchild nodes of the root node created by GetRoot.

Example 2. Using GetDescendant to construct a hierarchy.

DECLARE @Root hierarchyid
DECLARE @Child1 hierarchyid
DECLARE @Child2 hierarchyid
DECLARE @Child3 hierarchyid
DECLARE @Child4 hierarchyid
DECLARE @Grandchild1 hierarchyid
DECLARE @Grandchild2 hierarchyid

SET @Root = hierarchyid::GetRoot()
SET @Child1 = @Root.GetDescendant(NULL, NULL)
SET @Child2 = @Root.GetDescendant(@Child1, NULL)
SET @Child3 = @Root.GetDescendant(@Child1, @Child2)
SET @Child4 = @Root.GetDescendant(NULL, @Child1)
SET @Grandchild1 = @Child1.GetDescendant(NULL, NULL)
SET @Grandchild2 = @Child1.GetDescendant(@Grandchild1, NULL)

SELECT
 @Root AS Root,
 @Child1 AS Child1,
 @Child2 AS Child2,
 @Child3 AS Child3,
 @Child4 AS Child4,
 @Grandchild1 AS Grandchild1,
 @Grandchild2 AS Grandchild2

The output appears as follows:

Root Child1 Child2 Child3 Child4 Grandchild1 Grandchild2
---- ------ ------ ------ ------ ----------- -----------
0x   0x58   0x68   0x62C0 0x48   0x5AC0      0x5B40

(1 row(s) affected)