CLR Types - Pro SQL Server Internals - page 287

■ Coverage of HierarchyId data type methods is beyond the scope of this topic. You can learn more about the
HierarchyId data type at: http://technet.microsoft.com/en-us/library/bb677173.aspx .

Note

There are several techniques that allow us to store hierarchical information in a database. Let's look at the most
common ones:

Adjacency List. This is perhaps the most commonly used technique. It persists the
reference to the parent node in every child node. Such a structure is shown in Figure 14-11
and Listing 14-14.

Figure 14-11. Adjacency List

Listing 14-14. Adjancency List DDL

create table dbo.OrgChart
(
ID int not null,
Name nvarchar(64) not null,
Title nvarchar(64) not null,
ParentID int null,

constraint PK_OrgChart
primary key clustered(ID),

constraint FK_OrgChart_OrgChart
foreign key(ParentId)
references dbo.OrgChart(ID)
)

Closure Table. This is similar to an Adjacency List, however the parent-child relationship is
stored separately. Figure 14-12 and Listing 14-15 show an example of a Closure Table.