SQL Server - Discovering the HierarchyId data type

The hierarchyid type was introduced with SQL Server 2008. This type is used to represent and manipulate hierarchical data. Hierarchical data contain the notion of parent/child but also the notion of order between elements having the same parent. Values ​​of this type are stored in a binary form, for example, 0x5AC0 or 0x58, but in practice, we use character strings of the type '/' for the root or '/1/' for a child of the root node, And so on :

The purpose of this article is to present the current operations achievable with this type of data.

#Creating the table

Creating a hierarchyid column is similar to any other simple data types:

CREATE TABLE [dbo].[Employee] (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [hid] [hierarchyid] NOT NULL,
)

This type can also be used as a key:

CREATE TABLE [dbo].[Employee](
    [Id] [hierarchyid] NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL
)

#Inserting data

As noted at the beginning, there are several methods for manipulating the hierarchyid type. So, to create a hierarchyid object you can use:

• hierarchyid::Parse('/1/1')
• CAST('/1/' AS hierarchyid) ⇔ hierarchyid::Parse('/1/')
• hierarchyid::GetRoot() ⇔ hierarchyid::Parse('/')

To insert a row in the table we can use one of the following commands:

INSERT INTO Employee (FirstName, LastName, hid)
VALUES ('John', 'Doe', '/') -- implicit conversion to hierarchyid

INSERT INTO Employee (FirstName, LastName, hid)
VALUES ('John', 'Doe', hierarchyid::GetRoot())

INSERT INTO Employee(FirstName, LastName, hid)
VALUES ('John', 'Doe', hierarchyid::Parse('/1/'))

INSERT INTO Employee(FirstName, LastName, hid)
VALUES ('John', 'Doe', CONVERT('/1/' as hierarchyid))

#ToString

Because the hierarchyid type is stored in binary form, the result of the SELECT command is not very readable:

To make the result readable by a human, use the ToString method:

SELECT [Id], [FirstName], [LastName], [hid].ToString()
FROM [Employee]

#Comparing and sorting

The comparison between 2 hierarchyid values is as follows: Given two hierarchyid values a and b, a < b means a comes before b in a depth-first traversal of the tree. This sorting is intuitive and makes it easy to sort the data:

SELECT [Id], [FirstName], [LastName], [hid].ToString()
FROM [Employee]
ORDER BY [hid]

#GetDescendant

The GetDescendant method is used to create a hierarchyid corresponding to the child of another hierarchyid. This method has 2 arguments to specify the location of the child. You can create the child at the left of another child, at the right of another child, or between 2 children:

-- Create the first child of /1/ => /1/1/
SELECT CAST('/1/' AS hierarchyid).GetDescendant(NULL, NULL).ToString()

-- Create a child of /1/ after the child /1/1/ => /1/2/
SELECT CAST('/1/' AS hierarchyid).GetDescendant(CAST('/1/1/' AS hierarchyid), NULL).ToString()

-- Create a child of /1/ before the child /1/1/ => /1/0/
SELECT CAST('/1/' AS hierarchyid).GetDescendant(NULL, CAST('/1/1/' AS hierarchyid)).ToString()

-- Create a child of /1/ between the child /1/2/ and the child /1/3/ => /1/2.1/
SELECT CAST('/1/' AS hierarchyid).GetDescendant(CAST('/1/2/' AS hierarchyid), CAST('/1/3/' AS hierarchyid)).ToString()

Here's the final hierarchy:

#GetLevel

The GetLevel method allows you to know the depth of the element in the tree:

SELECT CAST('/' AS hierarchyid).GetLevel()       -- 0
SELECT CAST('/1/' AS hierarchyid).GetLevel()     -- 1
SELECT CAST('/1/1/' AS hierarchyid).GetLevel()   -- 2
SELECT CAST('/1/1.0/' AS hierarchyid).GetLevel() -- 2

You remember from the previous article on recursive CTEs to display the complete listing indented and sorted. With the hierarchyid type, the query is much more simpler:

SELECT REPLICATE('--', hid.GetLevel()) + FirstName + ' ' + LastName, hid.ToString()
FROM Employee
ORDER BY hid

#IsDescendantOf

The IsDescendantOf method, as its name indicates, indicates if a node is a descendant of another node (not only a direct child). This query retrieves all items whose parent or ancestor is /1/:

SELECT [Id], [FirstName], [LastName], [hid].ToString()
FROM [Employee]
WHERE [hid].IsDescendantOf(CAST('/1/' AS hierarchyid)) = 1

#GetAncestor

The GetAncestor method is used to create a hierarchyid corresponding to the specified parent level:

SELECT CAST('/1/2/3.5/' AS hierarchyid).GetAncestor(0).ToString()
 -- /1/2/3.5/

 SELECT CAST('/1/2/3.5/' AS hierarchyid).GetAncestor(1).ToString()
 -- /1/2/

 SELECT CAST('/1/2/3.5/' AS hierarchyid).GetAncestor(2).ToString()
 -- /1/

 SELECT CAST('/1/2/3.5/' AS hierarchyid).GetAncestor(3).ToString()
 -- /

 SELECT CAST('/1/2/3.5/' AS hierarchyid).GetAncestor(4).ToString()
 -- NULL

#Select all ancestors of an element

The first (but not the best) query that comes to mind to answer this problem is a recursive CTE:

WITH Ancestors(Id, FirstName, LastName, AncestorId)
AS
(
    SELECT Id, FirstName, LastName, hid.GetAncestor(1)
    FROM Employee
    WHERE hid = '/1/2/1/'

    UNION ALL

    SELECT e.Id, e.FirstName, e.LastName, e.hid.GetAncestor(1)
    FROM Employee e
    INNER JOIN Ancestors a
          ON e.hid = a.AncestorId
)
SELECT FirstName, LastName, AncestorId.ToString() FROM Ancestors

Now by rephrasing the question we realize that a simpler query exists: Select all the elements whose current element is the child:

SELECT Id, FirstName, LastName, hid.ToString()
FROM Employee
WHERE CAST('/1/2/1/' as hierarchyid).IsDescendantOf(hid) = 1

There is always more than one way to get to the result… But some are simpler than others 😃

#GetReparentedValue

The GetReparentedValue method is used to move an item in the tree.

DECLARE @employee as hierarchyid = '/1/1/3/'
DECLARE @oldparent as hierarchyid = '/1/1/'
DECLARE @newparent as hierarchyid = '/1/2/'

SELECT @employee.GetReparentedValue(@oldparent, @newparent).ToString()
-- /1/2/3/

To move a subtree we can use the following query:

DECLARE @nold hierarchyid = CAST('/1/' as hierarchyid) -- sub-tree to move
DECLARE @nnew hierarchyid = CAST('/2/' as hierarchyid) -- new parent

-- We want to move the tree under @nnew, after all its children
-- Compute the value of the new hierarchyid
SELECT @nnew = @nnew.GetDescendant(max(hid), NULL)
FROM Employee
WHERE hid.GetAncestor(1) = @nnew;

-- Update the children of @nold
UPDATE Employee
SET hid = hid.GetReparentedValue(@nold, @nnew)
WHERE hid.IsDescendantOf(@nold) = 1;

To avoid problems these lines must be placed in a transaction.

#Avoid nodes without a parent

The hierarchyid type indicates a position in a hierarchy but does not guarantee an element has a direct parent. The following instructions run without problems:

CREATE TABLE Employee
(
    Id hierarchyid NOT NULL PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
)
GO

INSERT INTO Employee(Id, Name) VALUES ('/', 'John')
INSERT INTO Employee(Id, Name) VALUES ('/1/', 'Jane')
INSERT INTO Employee(Id, Name) VALUES ('/1/1/', 'Jeff')
DELETE FROM Employee WHERE Id = CAST('/1/' AS hierarchyid) -- No error

Depending on your need, a node must have a parent (except the root element, of course). The solution is to add a computed and persisted column corresponding to the node's parent (GetAncestor(1) method). Then, you can add a foreign key between the computed column and the Id column, so the integrity is validated:

CREATE TABLE Employee
(
    Id hierarchyid NOT NULL PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
    ManagerId AS Id.GetAncestor(1) PERSISTED REFERENCES Employee(Id),
)
GO

INSERT INTO Employee(Id, Name) VALUES ('/', 'John')
INSERT INTO Employee(Id, Name) VALUES ('/2/', 'Jane')
INSERT INTO Employee(Id, Name) VALUES ('/2/1/', 'Jeff')
DELETE FROM Employee WHERE Id = CAST('/2/' AS hierarchyid) -- Error

#Conclusion

We have now gone around the methods of the hierarchyid object. We have seen that it allows to simplify some requests but it is important to know its limitations. One of them is its size, although potentially very large (maximum 892 bytes), is limited and therefore does not allow to save hierarchies of infinite size. There are other ways to create a hierarchy for example by interval representation of trees or closure tables. It's up to you to choose the one that best suits your needs.

Do you have a question or a suggestion about this post? Contact me!