You are a database developer on an instance of SQL Server 2008. You are designing an application to be used by the human resources department. Your Employee table needs to contain the following information:
• The employee’s unique identifier
• The employee’s last name and first name
• The numeric identifier of the department in which the employee works
• The unique employee identifier of the employee’s manager
• The date the employee was hired
You want to be able to easily navigate the organization chart using a tree-like structure and easily identify each employee’s level in the organization chart. You also want to minimize code complexity. Which action should you take?
A. Implement separate tables for the employees and the employees’ managers with a FOREIGN KEY constraint that relates the two tables.
B. Implement a single Employee table with the EmpID column as a hierarchyid data type.
C. Implement a single Employee table that represents each employee using an xml data type.
D. Implement a single Employee table that includes a FOREIGN KEY constraint on the MgrID column that references the EmpID column.
Correct Answer: B
Explanation/Reference:
The hierarchyid data type is a special variable-length, CLR-supplied data type that can be used to represent hierarchical data. When you define a hierarchyid data type column, you can use various system-supplied methods, such as GetRoot, GetDescendant, GetLevel, Read, and Write, which allow you 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, you could use the GetLevel method to easily return an employee’s level in the organization chart. Then, you 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 dbo.Employee (EmpID, HierID, LastName, FirstName, DeptID, HireDate, Salary)
VALUES(1, hierarchyid::GetRoot(), ‘Jones’, ‘Cindy’, 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 dbo.Employee (EmpID, HierID, LastName, FirstName, DeptID, HireDate, Salary)
VALUES (2, @parent.GetDescendant(NULL, NULL), ‘King’, ‘Scott’, 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 dbo.Employee (EmpID, HierID, LastName, FirstName, DeptID, HireDate, Salary)
VALUES (3, @parent.GetDescendant(NULL, NULL), ‘ Rodriguez’, ‘Raymond’, 45, GETDATE(), 37000.00)
GO
-- Query all employees including a string that represents the path in the hierarchy
SELECT HierID.ToString() AS ‘HierString’, *
FROM dbo.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/’.
You 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 you implemented separate tables for the employees and the employees’ managers, you would have to combine datasets each time you wanted to query all employees. If you 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.
You should not implement a single Employee table that represents each employee using an xml data type. An xml data type is used to store XML data, such as XML documents or XML document fragments.
