在SQL SERVER 2008中引入了一个新的类型，为hierarchyid,支持层次结构，hierarchyid从技术上讲是一个CLR UDT，但是在SQL SERVER 2008不必先启用它。
考虑下面的层次结构
可以用如下SQL语句表示
CREATE TABLE Employees (node hierarchyid PRIMARY KEY CLUSTERED, level as node.GetLevel() PERSISTED, employee_id INT UNIQUE, employee_name varchar(30) NOT NULL) insert into Employees Values(hierarchyid::GetRoot(),5000,'Rob') declare @ManagerNode hierarchyid declare @Level hierarchyid select @ManagerNode=node FROM Employees WHERE employee_id=5000 insert into Employees Values(@ManagerNode.GetDescendant(NULL,NULL),5001,'Bill') select @Level=node from Employees where employee_id=5001 insert into Employees values(@ManagerNode.GetDescendant(@Level,NULL),5002,'Steve') select node.ToString() as NodeAsString,node as NodeAsBinary, node.GetLevel() As Level,employee_id,employee_name from Employees declare @Level hierarchyid select @Level=node from Employees where employee_id=5002 insert into Employees values(@Level.GetDescendant(NULL,NULL),5003,'Jim') declare @child1 hierarchyid select @child1=node from Employees where employee_id=5003 insert into Employees values(@Level.GetDescendant(@child1,NULL),5004,'Steve') select @Level=node from Employees where employee_id=5001 insert into Employees values(@Level.GetDescendant(NULL,NULL),5005,'Joseph') select @child1=node from Employees where employee_id=5005 insert into Employees values(@Level.GetDescendant(@child1,NULL),5006,'Joan') select @Level=node from Employees where employee_id=5006 insert into Employees values(@Level.GetDescendant(NULL,NULL),5007,'Alice') select @child1=node from Employees where employee_id=5007 insert into Employees values(@Level.GetDescendant(@child1,NULL),5008,'Barbara') select node.ToString() as NodeAsString,node as NodeAsBinary, node.GetLevel() As Level,employee_id,employee_name from Employees
