SQL Server: Hierarchical Data Set

The hierarchyid data type is a special variable-length, CLR-supplied data type that can be used to represent hierarchical data. When DBA define a hierarchyid data type column, DBA can use various system-supplied methods, such as GetRoot, GetDescendant, GetLevel, Read, and Write, which allow DBA to perform tasks on the hierarchy. Each value stored in the table is an internal binary value that represents the row's position in the hierarchy. In this scenario, DBA could use the GetLevel method to easily return an employee's level in the organization chart. Then, DBA could use statements similar to the following to perform tasks in the hierarchy:

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

 -- Insert an employee that is a subordinate to EmpID 2

DECLARE @parent hierarchyid, @child hierarchyid

SELECT @parent = HierID FROM Employee WHERE EmpID = 2
INSERT 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

GO

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