Database Reference
In-Depth Information
Figure 14-14.
Materialized (hierarchical) Path
Listing 14-17.
Materialized Path DDL
create table dbo.OrgChart
(
ID int not null,
Name nvarchar(64) not null,
Title nvarchar(64) not null,
Path varchar(256) not null,
constraint PK_OrgChart
primary key clustered(ID),
);
Each approach has its own strengths and weaknesses. Adjacency Lists and Closure Tables are easy to
maintain—adding or removing new members to the hierarchy as well as subtree movement affects a single or very
small number of the nodes. However, querying those structures often requires recursive or imperative code.
In contrast, Nested Sets and Materialized Paths are very easy to query, although hierarchy maintenance is
expensive. For example, if you move the subtree to a different parent, you must update the corresponding bower or
path values for each child in the subtree.
HierarchyId
type uses the materialized path technique persisting relative path information similar to the
example shown in Figure
14-14
. The path information is stored in binary format. The actual storage space varies and
depends on a few factors. Each level in the hierarchy adds an additional node to the path and increases its size.
Another important factor is how a new
HierarchyId
value is generated. As already mentioned,
HierarchyId
stores the relative positions rather than the absolute key values from the nodes. As a result, if you need to add a
new child node at the rightmost node to the parent, you can increment the value from the former rightmost node.
However, if you need to add the node in between two existing nodes, that would require persisting additional
information in the path. Figure
14-15
shows an example of this.
