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 ...
Get Programming Microsoft® SQL Server™ 2008 now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.