Programming Microsoft® SQL Server™ 2008

Creating a Hierarchical Table

Let’s begin by implementing the classic manager-employee tree scenario using the new hierarchyid data type. We start with the structure of the Employee table, as shown in Example 7-1.

Example 7-1. Creating a hierarchical table

CREATE DATABASE MyDB
GO

USE MyDB
GO

CREATE TABLE Employee
(
   NodeId        hierarchyid PRIMARY KEY CLUSTERED,
   NodeLevel     AS NodeId.GetLevel(),
   EmployeeId    int UNIQUE NOT NULL,
   EmployeeName  varchar(20) NOT NULL,
   Title         varchar(20) NULL
)
GO

Notice that the NodeId column is declared as the new hierarchyid type and is also defined as the table’s primary key using a clustered index. The primary key is important for two reasons, the first of which is that SQL Server doesn’t guarantee uniqueness of hierarchyid ...