-- Insert an employee into the
table at the root node of the hierarchy
-- GetRoot returns the appropriate hierarchyid to store in the HierID column
INSERT INTO
abc.Employee (EmpID, HierID, LastName, FirstName, DeptID, HireDate, Salary)
VALUES(1, hierarchyid::GetRoot(), 'Fayyaz', 'Ahmed', 45,
GETDATE(), 97000.00);
GO
-- Insert an employee that is a subordinate to the root
-- GetRoot returns the
hierarchyid of the root
-- GetDescendant returns the hierarchyid of the new employee's manager
DECLARE @parent hierarchyid
SELECT @parent = hierarchyid::GetRoot()
INSERT INTO abc.Employee (EmpID, HierID, LastName, FirstName, DeptID, HireDate,
Salary)
VALUES (2, @parent.GetDescendant(NULL, NULL), 'Sheikh', 'Ahmed', 45, GETDATE(), 52000.00)
GO
DECLARE @parent hierarchyid,
@child hierarchyid
SELECT @parent = HierID FROM
Employee WHERE EmpID = 2INSERT INTO abc.Employee (EmpID, HierID, LastName, FirstName, DeptID, HireDate,
Salary)
VALUES (3, @parent.GetDescendant(NULL, NULL), 'Saad', 'Ahmed', 45,
GETDATE (), 37000.00)
GO
-- Query all employees including a string that represents the path in the hierarchy
SELECT HierID.ToString() AS
'HierString', * FROM abc.Employee
GOThe ToString method in the final SELECT statement converts the employee's hierarchical value to a string. The root employee will have a HierString value of '/'. The employee with an EmpID value of 3 has a HierString value of '/1/1/'. DBA should not implement separate tables for the employees and the employees' managers with a FOREIGN KEY constraint that relates the two tables, or implement a single Employee table that includes a FOREIGN KEY constraint on the MgrID column that references the EmpID column. In this scenario, using a hierarchyid data type would provide the desired functionality and be less complex to implement. If DBA implemented separate tables for the employees and the employees' managers, DBA would have to combine datasets each time DBA wanted to query all employees. If DBA implemented a single Employee table that included a FOREIGN KEY constraint on the MgrID column that references the EmpID column, it would require a single join to return an employee's manager, but determining the employee's level in the organization chart would be extremely complex to code.DBA should not implement a single Employee table that represents each employee using an xml data type. An xml data type is used to store XMLdata, such as XML documents or XML document fragments.
