SQL Server 2008 (KATMAI) TSQL Enhancements - Part 8 - HIERARCHYID (Part 2)

In the previous post, we have examined the new HIERARCHYID data type introduced by SQL Server 2008. HIERARCHYID is a CLR data type and it supports a number of methods.

Note that the methods of CLR data types are case sensitive. Most of us, who do not bother much about the casing should be very careful working with these types. So, pay special attention to the correct casing while working with CLR data types.

We will examine the methods supported by HIERARCHYID data type in this post.

HIERARCHYID exposes the following methods.

1. GetAncestor()
2. GetDescendant()
3. GetLevel()
4. GetRoot()
5. ToString()
6. IsDescendantOf()
7. Parse()
8. Read()
9. GetReparentedValue()
10. Write()

We have seen some of these methods in the previous post. Let us have a closer look into each of these methods. Before we examine those methods and see them in action, we need to create a table and populate it with some hierarchical data. Run the code given in the previous post to create a table named 'employees' and populate it.

GetAncestor()

This method returns the HIERARCHYID of the parent of a given node. This method takes an argument which specifies the number of levels to go up. For example, GetAncestor(0) returns the node itself, and GetAncestor(1) returns the immediate parent. GetAncestor(2) returns the parent's parent (Parent at 2 levels up). Let us test this.

DECLARE @h HIERARCHYID
SELECT @h = EmployeeNode FROM employees WHERE EmployeeName = 'Andrew'
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h.GetAncestor(0)
/*
EmployeeName
--------------------
Andrew 
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h.GetAncestor(1)
/*
EmployeeName
--------------------
TechLead4
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h.GetAncestor(2)
/*
EmployeeName
--------------------
PM2
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h.GetAncestor(3)
/*
EmployeeName
--------------------
CEO
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h.GetAncestor(4)
/*
EmployeeName
--------------------

*/

Note that GetAncestor(4) did not return data. There are only 3 levels exist above the node of 'Andrew' and hence GetAncestor(4) returns NULL.

Note: If you pass a value higher than the number of levels in the hierarchy, you will get an empty result set. However, if you pass a negative number, SQL Server will generate an error.

In the previous examples we applied a WHERE condition to find out "Who is the ancestor of X". This helped us to traverse the hierarchy upward. Now let us see the reverse of this. Let us find out "Whose ancestor is X?" which will help us to traverse the hierarchy downward. Let us see a few examples.

DECLARE @h HIERARCHYID
SELECT @h = EmployeeNode FROM employees WHERE EmployeeName = 'PM2'
SELECT EmployeeName FROM employees WHERE EmployeeNode.GetAncestor(1) = @h
/*
EmployeeName
--------------------
TechLead3        
TechLead4 
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode.GetAncestor(2) = @h
/*
EmployeeName
--------------------
Laura            
Jeff             
Bill             
Andrew  
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode.GetAncestor(3) = @h
/*
EmployeeName
--------------------

*/

Note that the last query did not return anything. If the level does not exist, an empty result set is returned. However, if you pass a negative number, SQL Server will generate an error.

GetRoot()

This is a static method. The word "static" may not be that familiar to many SQL Server developers. 'static' methods can be invoked without instantiating an instance of an object. GetRoot() returns a HIERARCHYID that points to the root of any hierarchy. Let us see an example.

DECLARE @h HIERARCHYID = HierarchyID::GetRoot()
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h
/*
EmployeeName
--------------------
CEO              
*/

Instead of assigning the result of the GetRoot() method a variable, we could even use it directly in the WHERE clause. Here is an example.

SELECT EmployeeName FROM employees WHERE EmployeeNode = HierarchyID::GetRoot()
/*
EmployeeName
--------------------
CEO
*/

GetLevel()

GetLevel() returns the level of a given node in the hierarchy. To understand this better, let us look at a few examples.

SELECT EmployeeName FROM employees WHERE EmployeeNode.GetLevel() = 0
/*
EmployeeName
--------------------
CEO  
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode.GetLevel() = 1
/*
EmployeeName
--------------------
PM1              
PM2
*/
SELECT EmployeeName, EmployeeNode.GetLevel() AS Level
FROM employees
ORDER BY EmployeeNode.GetLevel()
/*
EmployeeName         Level
-------------------- ------
CEO                  0
PM1                  1
PM2                  1
TechLead3            2
TechLead1            2
TechLead2            2
TechLead4            2
Bill                 3
Andrew               3
Bob                  3
Mike                 3
Jacob                3
Steve                3
Laura                3
Jeff                 3
*/

We will examine the other methods in a future post.

SQL Server 2008 (KATMAI) TSQL Enhancements

1. SQL Server 2008 (KATMAI) TSQL Enhancements -
   Part 1 - Variable initialization and Compound Assignment Operators
   
2. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 2 - Row Constructors
   
3. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 3 - New Data Type: DATE
   
4. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 4 - New Data Type: TIME
   
5. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 5 - New Data Type: DATETIME2
   
6. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 6 - DATETIMEOFFSET
   
7. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 7 - HIERARCHYID (Part 1)