HierarchyID data type Methods

In my previous article that you can find here I introduced you to the Hierarchical data in MS SQL. HierarchyID data type is a cool if you need to store and handle hierarchical data in your database (e.g. organizational chart).

Here we will see and explain methods available for the manipulation of the HierarchyID data type.

For most methods we will use a test table and populate it with some test data. The test table and the data look like this:

CREATE TABLE [dbo].[Hierarchy](
	[ID] [int] NOT NULL,
	[Hierarchy] [hierarchyid] NULL,
	[Role] [varchar](256) NOT NULL,
	[BirthDate] [date] NOT NULL,
	CONSTRAINT [PK_Hierarchy_HID] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Hierarchy] ([ID], [Hierarchy], [Role], [BirthDate]) 
VALUES 
	(1, '/', 'Chief Executive Officer', '1969-01-29')
	, (2, '/1/', 'Vice President of Engineering', '1971-08-01')
	...
	, (25, '/5/1/1/4/', 'Production Technician 3', '1983-01-07')
GO

SELECT * FROM [dbo].[Hierarchy];
GO

GetAncestor()

GetAncestor() method can be used to return the ancestor node of a given hierarchical node at the number of levels based on an input parameter to the method.

In our example you can use it to return someone boss. In other words GetAncestor() can return the parent, grandparent, etc. of the desired node.

Syntax:

node.GetAncestor(n)

  • n - An int, representing the number of levels to go up in the hierarchy

The expression returns the value of hierarchyid data type

On our example, let’s find to whom the “Design Engineer” reports:

SELECT 
	H.*
FROM
	[dbo].[Hierarchy] H
	INNER JOIN [dbo].[Hierarchy] C ON 
		C.Hierarchy.GetAncestor(1) = H.Hierarchy 
		AND C. Role = 'Design Engineer';

In the second example we will find role to whom every role directly reports:

SELECT 
	C.ID
	, C.Role
	, H.Role AS ReportsTo
FROM
	[dbo].[Hierarchy] C
	LEFT JOIN [dbo].[Hierarchy] H ON 
		C.Hierarchy.GetAncestor(1) = H.Hierarchy;

GetDescendant()

If you would like to insert a new node into the hierarchy, between existing nodes, the GetDescendant() method helps you to do it. The method accepts two parameters, both of which can be NULL and represent existing child nodes.

In short words, the method GetDescendant() returns one child node that is a descendant of the parent.

Syntax:

parent.GetDescendant (child1, child2) 

  • child1 - NULL or the hierarchyid of a child of the current node
  • child2 - NULL or the hierarchyid of a child of the current node

The expression returns the value of hierarchyid data type. It generates a node value, using the following rules:

  • If parent is NULL, returns NULL,
  • If parent is not NULL, and both child1 and child2 are NULL, returns a child of parent,
  • If parent and child1 are not NULL, and child2 is NULL, returns a child of parent greater than child1,
  • If parent and child2 are not NULL and child1 is NULL, returns a child of parent less than child2,
  • If parent, child1, and child2 are not NULL, returns a child of parent greater than child1 and less than child2,
  • If child1 is not NULL and not a child of parent, an exception is raised,
  • If child2 is not NULL and not a child of parent, an exception is raised,
  • If child1 >= child2, an exception is raised.

Let's jump on some examples:

DECLARE @Manager hierarchyid, @Child1 hierarchyid, @Child2 hierarchyid;   
SET @Manager = CAST('/' AS hierarchyid);  
SET @Child1 = CAST('/5/' AS hierarchyid);  
SET @Child2 = CAST('/6/' AS hierarchyid);  

-- Parrent is NULL -> NULL
SELECT CAST(NULL AS hierarchyid).GetDescendant(NULL, NULL).ToString() AS NewHierarchyValue;
-- Return the first child of the parrent node -> /1/
SELECT @Manager.GetDescendant(NULL, NULL).ToString() AS NewHierarchyValue;
-- Return the first child of the parrent node after /5/ -> /6/
SELECT @Manager.GetDescendant(@Child1, NULL).ToString() AS NewHierarchyValue;
-- Return the first child of the parrent node before /6/ -> /5/
SELECT @Manager.GetDescendant(NULL, @Child2).ToString() AS NewHierarchyValue;
-- Return the first child of the parrent node between the nodes /5/ and /6/ -> /5.1/
SELECT @Manager.GetDescendant(@Child1, @Child2).ToString() AS NewHierarchyValue;

And now, let’s see also the exceptions:

DECLARE @Manager hierarchyid, @Child1 hierarchyid, @Child2 hierarchyid;  
-- child1 is not NULL and not a child of parent
SET @Manager = CAST('/' AS hierarchyid);  
SET @Child1 = CAST('/5/1/' AS hierarchyid);  
SELECT @Manager.GetDescendant(@Child1, NULL).ToString() AS NewHierarchyValue;
-- child2 is not NULL and not a child of parent
SET @Manager = CAST('/' AS hierarchyid);  
SET @Child2 = CAST('/6/1/' AS hierarchyid); 
SELECT @Manager.GetDescendant(NULL, @Child2).ToString() AS NewHierarchyValue;
-- child1 >= child2
SET @Manager = CAST('/' AS hierarchyid);  
SET @Child1 = CAST('/6/' AS hierarchyid);  
SET @Child2 = CAST('/5/' AS hierarchyid);  
SELECT @Manager.GetDescendant(@Child1, @Child2).ToString() AS NewHierarchyValue;

"1) Msg 6522, Level 16, State 2, Line 120
A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid": Microsoft.SqlServer.Types.HierarchyIdException: 24008: SqlHierarchyId.GetDescendant failed because 'child1' must be a child of 'this'.  'child1' was '/5/1/' and 'this' was '/'. Microsoft.SqlServer.Types.HierarchyIdException: at Microsoft.SqlServer.Types.ex_raise(Int32 major, Int32 minor, Int32 sev, Int32 state, Object param1, Object param2, Object param3) at Microsoft.SqlServer.Types.OrdPath.ExtractOrdComponentFake(OrdPath parent, Boolean fLeft, UInt16& bitOffset, Int64& ord, levelType& type) at Microsoft.SqlServer.Types.XmlIdGenerator.Init(OrdPath pordpathParent, OrdPath pordpathLeft, OrdPath pordpathRight) at Microsoft.SqlServer.Types.SqlHierarchyId.GetDescendant(SqlHierarchyId child1, SqlHierarchyId child2)
2) Msg 6522, Level 16, State 2, Line 124
A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid": Microsoft.SqlServer.Types.HierarchyIdException: 24008: SqlHierarchyId.GetDescendant failed because 'child2' must be a child of 'this'.  'child2' was '/6/1/' and 'this' was '/'. Microsoft.SqlServer.Types.HierarchyIdException: at Microsoft.SqlServer.Types.ex_raise(Int32 major, Int32 minor, Int32 sev, Int32 state, Object param1, Object param2, Object param3) at Microsoft.SqlServer.Types.OrdPath.ExtractOrdComponentFake(OrdPath parent, Boolean fLeft, UInt16& bitOffset, Int64& ord, levelType& type) at Microsoft.SqlServer.Types.XmlIdGenerator.Init(OrdPath pordpathParent, OrdPath pordpathLeft, OrdPath pordpathRight) at Microsoft.SqlServer.Types.SqlHierarchyId.GetDescendant(SqlHierarchyId child1, SqlHierarchyId child2)
3) Msg 6522, Level 16, State 2, Line 129
A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid": Microsoft.SqlServer.Types.HierarchyIdException: 24007: SqlHierarchyId.GetDescendant failed because 'child1' must be less than 'child2'.  'child1' was '/6/' and 'child2' was '/5/'. Microsoft.SqlServer.Types.HierarchyIdException:  at Microsoft.SqlServer.Types.ex_raise(Int32 major, Int32 minor, Int32 sev, Int32 state, Object param1, Object param2, Object param3) at Microsoft.SqlServer.Types.XmlIdGenerator.Init(OrdPath pordpathParent, OrdPath pordpathLeft, OrdPath pordpathRight) at Microsoft.SqlServer.Types.SqlHierarchyId.GetDescendant(SqlHierarchyId child1, SqlHierarchyId child2)"

The exception messages are clear and easy to understand.

GetLevel()

GetLevel() method returns the level of the hierarchy of a particular node. It is an integer value that represents the depth of the node in the tree.

Syntax:

node.GetLevel()

The expression returns the value of smallint data type

You can use it for example to get members on some level or to find out on what level is the desired node. The root of the hierarchy is level = 0.

Get the level of every record:

SELECT 
	A.[ID]
	, A.[Hierarchy]
	, A.[Role]
	, A.[BirthDate]
	, A.Hierarchy.GetLevel() AS [Level]
FROM
	[dbo].[Hierarchy] A;

Find all items on level 4 of the hierarchy:

SELECT 
	A.[ID]
	, A.[Hierarchy]
	, A.[Role]
	, A.[BirthDate]
	, A.Hierarchy.GetLevel() AS [Level]
FROM
	[dbo].[Hierarchy] A
WHERE
	A.[Hierarchy].GetLevel() = 4;

GetReparentedValue()

If you need to make changes to the hierarchy and move nodes from one parent to another you can use the GetReparentedValue() to calculate the new path.

Syntax

node.GetReparentedValue(oldRoot, newRoot)

  • oldRoot - A hierarchyid that is the node that represents the level of the hierarchy that is to be modified
  • newRoot - A hierarchyid that represents the node. Replace the oldRoot section of the current node to move the node

The expression returns the value of hierarchyid data type.

Let’s see one example. Imagine on our example that “Production Control Manager” will not be an active role/employee in our company. We created a new position named “Production manager” and this node has a hierarchy value of “/6/”. Let’s calculate the new hierarchy for all nodes that are currently “/5/” or descendants:

DECLARE @OldParent hierarchyid, @NewParent hierarchyid;   
SET @OldParent = CAST('/5/1/1/' AS hierarchyid);  
SET @NewParent = CAST('/6/' AS hierarchyid);  

SELECT 
	ID
	, Hierarchy
	, Hierarchy.ToString() AS HierarchyString
	, Role
	, Hierarchy.GetReparentedValue(@OldParent, @NewParent).ToString() AS NewHierarchyString
FROM 
	Hierarchy 
WHERE 
	Hierarchy.IsDescendantOf(0x8D6B) = 1;

If for example the old parent node was not the actual parent node of the node you would like to move, you will get an exception:

DECLARE @Node hierarchyid, @OldParent hierarchyid, @NewParent hierarchyid;   
SET @Node = CAST('/6/' AS hierarchyid);  
SET @OldParent = CAST('/5/' AS hierarchyid);  
SET @NewParent = CAST('/6/' AS hierarchyid);  

SELECT @Node.GetReparentedValue(@OldParent, @NewParent).ToString();

"Msg 6522, Level 16, State 2, Line 189
A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid": Microsoft.SqlServer.Types.HierarchyIdException: 24009: SqlHierarchyId.GetReparentedValue failed because 'oldRoot' was not an ancestor node of 'this'.  'oldRoot' was '/5/', and 'this' was '/6/'. Microsoft.SqlServer.Types.HierarchyIdException:  at Microsoft.SqlServer.Types.SqlHierarchyId.GetReparentedValue(SqlHierarchyId oldRoot, SqlHierarchyId newRoot)"

GetRoot()

GetRoot() method returns the root of the hierarchy tree. GetRoot() is a static method.

Syntax:

hierarchyid::GetRoot()

The expression returns the value of hierarchyid data type

So, let's get the root node of the test data:

SELECT 
	A.[ID]
	, A.[Hierarchy]
	, A.[Role]
	, A.[BirthDate]	
FROM
	[dbo].[Hierarchy] A
WHERE
	A.Hierarchy = hierarchyid::GetRoot();

IsDescendantOf()

IsDescendantOf() method evaluates if a node within the hierarchy is a descendant (at any level) of a node that is passed to it as a parameter.

Syntax:

node.IsDescendantOf(parent)

  • parent - hierarchyid of the node for which the IsDescendantOf test should be performed

The expression returns the value of bit (boolean) data type representing if the node is a descendant of the parent node).

Let's get all employees in "Production department":

SELECT 
	A.[ID]
	, A.[Hierarchy]
	, A.[Role]
	, A.[BirthDate]	
FROM 
	[dbo].[Hierarchy] A
WHERE
	A.Hierarchy.IsDescendantOf(0x8C) = 1;

Or check for every employee if it is working in the "Production department":

SELECT 
	A.[ID]
	, A.[Hierarchy]
	, A.[Role]
	, A.[BirthDate]	
	, A.Hierarchy.IsDescendantOf(0x8C) AS ProductionDepartment
FROM 
	[dbo].[Hierarchy] A;

Parse()

Parse() method performs the reverse process of the ToString() method. It is called implicitly when a string representation of a node is inserted into a HierarchyID column.

Syntax:

hierarchyid::Parse(input)

  • input - The character data type value that is being converted

The expression returns the value of hierarchyid data type.

Parse() attempts to convert a string formatted representation to the HierarchyID representation. If it fails, an error is thrown.

Example:

DECLARE @StringValue AS nvarchar(4000) = 'a';
SELECT hierarchyid::Parse(@StringValue) AS hierarchyidRepresentation;

“Msg 6522, Level 16, State 2, Line 183
A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid": Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse failed because the input string 'a' is not a valid string representation of a SqlHierarchyId node. Microsoft.SqlServer.Types.HierarchyIdException: at Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input)

Here is a good example:

DECLARE @StringValue AS nvarchar(4000) = '/1/1/1/';
SELECT hierarchyid::Parse(@StringValue) AS hierarchyidRepresentation;

It is called implicitly when a value is casted to hierarchyid data type.

DECLARE @StringValue AS nvarchar(4000) = '/1/1/1/';
SELECT CAST(@StringValue AS hierarchyid) AS hierarchyidRepresentation;

ToString()

HierarchyID data type is written in the database (and displayed when selecting the value) using a hexadecimal value. If you would like to display the stored values in a human readable (string) form than you should use the ToString() method.

Syntax:

node.ToString()

The expression returns the value of nvarchar(4000) data type

SELECT
	C.ID
	, C.Hierarchy AS StoredValue
	, C.Hierarchy.ToString() AS HumanReadebleValue
	, C.Role
	, C.BirthDate
FROM
	[dbo].[Hierarchy] C;

You can accomplish the same if you just cast the HierarchyID data type to nvarchar(4000) because in this case SQL Server engine will implicitly call the ToString() method.

SELECT
	C.ID
	, C.Hierarchy AS StoredValue
	, CAST(C.Hierarchy AS nvarchar(4000)) AS HumanReadebleValue
	, C.Role
	, C.BirthDate
FROM
	[dbo].[Hierarchy] C;

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (7.42 kb)