Programming Microsoft® SQL Server® 2012

Creating a Hierarchical Table

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

Example 7-1. Creating a hierarchical table.

USE master
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MyDB')
 DROP DATABASE MyDB
GO
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 a hierarchyid type and is also defined as the table’s primary key using a clustered index. The primary key is important for two reasons, ...