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