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 ( NodeIdhierarchyid
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, ...
Get Programming Microsoft® SQL Server® 2012 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.