Populating the Hierarchy

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

Hierarchical organizational chart.

Figure 7-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 ...

Get Programming Microsoft® SQL Server® 2012 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.