HierarchyId

Most people who have worked with databases have needed to model a hierarchy in their data. One I’ve seen a lot is modeling a hierarchy at a company, recording who a person reports to. Anytime I’ve needed to do this, I’ve used an adjacency list, which ends up being implemented as storing ID of the user that the person reports to on their record. It’s a fast write, and you’re able to maintain data integrity by creating a self-referencing foreign key. Reading is a little more difficult, especially if you need to go up several levels (Although it’s easy enough to put together a CTE for that).
I have seen some people list the adjacency list as an anti-pattern. Karwin recommends implementing a path structure, which is the method used by the HierarchyID type in SQL Server. This feature was added in SQL Server 2008, but I have to admit that I didn’t spend too much time getting familiar with it. It seemed a bit over-complicated to me at the time, but I wanted to go back and dig a little deeper into this feature.

Setup:
We’ll create an Employee table, with a HierarchyID column. We’ll make the column unique and not-nullable.

DROP TABLE IF EXISTS dbo.Employee;

CREATE TABLE dbo.Employee(
	ID int NOT NULL IDENTITY (1,1) PRIMARY KEY,
	FirstName varchar(30) NOT NULL,
	LastName varchar(30) NOT NULL,
	ReportsTo hierarchyid NOT NULL UNIQUE,
	IsActive bit NOT NULL DEFAULT 1
);

INSERT INTO dbo.Employee(FirstName, LastName, ReportsTo) 
	VALUES ('Big', 'Boss', '/');
INSERT INTO dbo.Employee(FirstName, LastName, ReportsTo) 
	VALUES ('Jane', 'Doe', '/1/');
INSERT INTO dbo.Employee(FirstName, LastName, ReportsTo) 
	VALUES ('John', 'Doe', '/1/1/');
INSERT INTO dbo.Employee(FirstName, LastName, ReportsTo) 
	VALUES ('Mike', 'Smith', '/1/1/1/');
INSERT INTO dbo.Employee(FirstName, LastName, ReportsTo) 
	VALUES ('Sally', 'Jones', '/2/');
INSERT INTO dbo.Employee(FirstName, LastName, ReportsTo) 
	VALUES ('Tom', 'Jackson', '/2/1/');
INSERT INTO dbo.Employee(FirstName, LastName, ReportsTo) 
	VALUES ('Chris', 'Johnson', '/3/');
INSERT INTO dbo.Employee(FirstName, LastName, ReportsTo) 
	VALUES ('Michelle', 'Williams', '/3/1/');

SELECT * FROM dbo.Employee;

The ReportsTo column will store the hierarchy. The person at the top will just have the slash. People one level down from them will have a number; /1/, /2/, etc. Someone on the third level will have the value of the person they report to, plus their own value. In this example, John Doe reports to Jane Doe. Jane’s ID is /1/, so John will have /1/1/.

Retrieving Data:
When we select all of the records from the table, we see that we don’t actually see the hierarchy values, we see a Hex value instead.
We can call the ToString method for that column to show the path (These Hierarchy methods are case sensitive):

SELECT FirstName, LastName, ReportsTo.ToString() as HierarchyPath
FROM dbo.Employee;

We can also call the GetLevel method to see which level each employee is on:

SELECT FirstName, LastName, ReportsTo.GetLevel() as HierarchyLevel
FROM dbo.Employee;

The IsDescendantOf method will return everyone that is under a given node, including the specified node:

DECLARE @ID hierarchyid;
SELECT @ID = ReportsTo FROM dbo.Employee WHERE FirstName = 'Jane' and LastName = 'Doe'

SELECT FirstName, LastName, ReportsTo.ToString() as HierarchyPath
FROM dbo.Employee
WHERE ReportsTo.IsDescendantOf(@ID) = 1;

This query will return Jane Doe, John Doe, and Mike Smith.

Let’s insert an additional record, a new employee that reports to John Doe and is on the same level as Mike Smith:

DECLARE @ID hierarchyid;
SELECT @ID = ReportsTo FROM dbo.Employee 
	WHERE FirstName = 'Mike' and LastName = 'Smith';

INSERT INTO dbo.Employee(FirstName, LastName, ReportsTo)
SELECT 'Matt', 'Ryan', 
	ReportsTo.GetDescendant(@ID, NULL).ToString()
FROM dbo.Employee
WHERE FirstName = 'John' 
	AND LastName = 'Doe';

We can see everyone that reports to a given person by using the GetAncestor method. The method takes a parameter that specifies how many levels down to go. For example, this query will return both people that directly report to John Doe:

DECLARE @ID hierarchyid;
SELECT @ID = ReportsTo FROM dbo.Employee
WHERE FirstName = 'John' and LastName = 'Doe';

SELECT FirstName, LastName, ReportsTo.ToString() as HierarchyPath
FROM dbo.Employee
WHERE ReportsTo.GetAncestor(1) = @ID;

Wrap-up:
That’s a basic overview of the HierarchyID data type. It’s definitely a bit of a learning curve to get the hang of it. Whether it’s worth the effort depends on what you need out of your data.
HierarchyID makes things easier if you need to know everyone at a certain level, or returning everyone under a given person at particular levels.

Links:

Codingsight: How to Use SQL Server HierarchyID Through Easy Examples

SQL Shack: Use of hierarchyid in SQL Server

MS SQL Tips: SQL Server Hierarchyid Data Type Overview and Examples