SQL Server HierarchyId demo

hierarchyid.sql

-------------------------------------------------------------------------------
-- Using linked IDs
-------------------------------------------------------------------------------

-- Create table
CREATE TABLE Categories (
	[Id]		int	IDENTITY	NOT NULL,
	[ParentId]	int				NULL,
	[Name]		nvarchar(100)	NOT NULL,

	CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ([Id] ASC),
	CONSTRAINT [FK_Categories_ParentId] FOREIGN KEY ([ParentId]) REFERENCES [Categories] ([Id])
)
GO

-- Insert category data to database
SET NOCOUNT ON
SET IDENTITY_INSERT [Categories] ON
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (1, NULL, 'IT Profesionálové')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (2, 1, 'Systémová správa a administrace')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (3, 2, 'Microsoft Azure')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (4, 2, 'Microsoft 365')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (5, 2, 'Windows 10')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (6, 2, 'Windows Server')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (7, 2, 'Unix/Linux')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (8, 2, 'Smělá výzva')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (9, 2, 'System Center')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (10, 2, 'SUSE')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (11, 2, 'Red Hat Enterprise Linux')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (12, 1, 'Virtualizace a Cloud')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (13, 12, 'Microsoft Azure')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (14, 1, 'Automatizované testování')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (15, 1, 'Networking')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (16, NULL, 'IT bezpečnost a Hacking')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (17, 16, 'Bezpečnost systémů a aplikací')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (18, 16, 'IT bezpečnost a Hacking')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (19, 16, 'Kryptování')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (20, 16, 'Audity a standardy')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (21, NULL, 'Data')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (22, 21, 'Relační databáze')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (23, 22, 'Microsoft SQL Server')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (24, 22, 'Microsoft Azure')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (25, 22, 'Oracle')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (26, 22, 'MySQL')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (27, 22, 'PostgreSQL')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (28, 22, 'Firebird')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (29, 21, 'Big Data / NoSQL / AI+ML')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (30, 21, 'Business Intelligence')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (31, 30, 'Microsoft SQL Server')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (32, 30, 'Microsoft Fabric')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (33, 30, 'Microsoft Power BI')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (34, 21, 'Internet of Things (IoT)')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (35, 21, 'Řízení a ochrana dat')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (36, NULL, 'Web a Grafika')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (37, 36, '3D grafika')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (38, 36, 'CAD')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (39, 36, 'Grafika a sazba')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (40, 36, 'Fotografie')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (41, 36, 'Video animace a multimédia')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (42, 36, 'Kancelářská grafika')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (43, 36, 'Tvorba webových stránek')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (44, 36, 'Internetový marketing')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (45, NULL, 'Projekty / Procesy')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (46, 45, 'PRINCE 2')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (47, 45, 'ITIL')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (48, 45, 'COBIT')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (49, 45, 'DevOps')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (50, 45, 'Projektové řízení')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (51, 45, 'Procesní řízení')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (52, 45, 'Řízení IT')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (53, 45, 'Řízení kvality')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (54, 45, 'Osobní rozvoj')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (55, 45, 'Project management')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (56, 45, 'SAFe')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (57, 45, 'SCRUM / Agile')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (58, NULL, 'Programování')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (59, 58, 'AI')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (60, 58, 'Robotická automatizace procesů (RPA)')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (61, 58, 'Business analýza')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (62, 58, 'Design a architektura')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (63, 58, 'Project management')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (64, 58, 'Scrum')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (65, 58, 'ADO.NET')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (66, 58, 'ASP.NET')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (67, 58, 'Azure')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (68, 58, 'SharePoint')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (69, 58, 'DOTNET')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (70, 58, 'Jazyk C/C++')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (71, 58, 'Jazyk C#')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (72, 58, 'Jazyk JAVA')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (73, 58, 'Jazyk JavaScript')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (74, 58, 'Jazyk Kotlin')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (75, 58, 'Jazyk MATLAB')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (76, 58, 'Jazyk Perl')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (77, 58, 'Jazyk PHP')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (78, 58, 'Jazyk Python')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (79, 58, 'Jazyk R')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (80, 58, 'Jazyk Scala')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (81, 58, 'Jazyk TypeScript')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (82, 58, 'Jazyk VB.NET')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (83, 58, 'Jazyk UML')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (84, 58, 'Jazyk XML')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (85, 58, 'Kubernetes')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (86, 58, 'MS Office aplikace (Excel/Access)')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (87, 58, 'Mobilní aplikace')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (88, 58, 'Ostatní programování')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (89, NULL, 'Kancelář')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (90, 89, 'Základy práce s PC')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (91, 89, 'Umělá inteligence (AI)')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (92, 89, 'Kancelářské programy')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (93, 92, 'Microsoft Excel')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (94, 92, 'Microsoft Access')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (95, 92, 'Microsoft Outlook')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (96, 92, 'Microsoft PowerPoint')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (97, 92, 'Microsoft Project')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (98, 92, 'Microsoft SharePoint Server')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (99, 92, 'Microsoft Visio')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (100, 92, 'Microsoft Word')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (101, 92, 'Ostatní kurzy Microsoft Office')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (102, 92, 'Další kurzy pro kancelář')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (103, 92, 'Google')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (104, 89, 'BI/Power BI')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (105, 89, 'MS Office 365')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (106, 89, 'Apple')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (107, NULL, 'Podnikové systémy')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (108, 107, 'Microsoft Dynamics 365')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (109, 107, 'Microsoft Power Platform')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (110, 107, 'Architektura a modelování')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (111, NULL, 'Konference	 semináře')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (112, NULL, 'Certnexus')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (113, NULL, 'Oracle')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (114, 113, 'Jazyky SQL a PL/SQL')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (115, 113, 'Oracle Administrace')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (116, 113, 'Data Guard')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (117, 113, 'WEBLogic')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (118, 113, 'Jazyk JAVA')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (119, 113, 'SOLARIS')
INSERT INTO Categories ([Id], [ParentId], [Name]) VALUES (120, 113, 'Oracle ostatní')
SET IDENTITY_INSERT [Categories] OFF
GO

-- Get top level categories
SELECT * FROM Categories WHERE ParentId IS NULL
GO

-- Get categories under ID=1
SELECT * FROM Categories WHERE ParentId=1
GO

-- Clean up
DROP TABLE Categories
GO

-------------------------------------------------------------------------------
-- Using HierarchyId
-- https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference
-------------------------------------------------------------------------------

-- Create table with hierarchyid column
CREATE TABLE Employees (
	[Id]		int IDENTITY	NOT NULL,
	[OrgPos]	hierarchyid		NOT NULL,
	[Name]		nvarchar(100)	NOT NULL,
	[Title]		nvarchar(100)	NOT NULL,
	[OrgPosString]	AS [OrgPos].ToString(),
	[OrgPosLevel]	AS [OrgPos].GetLevel(),

	CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO

-- Insert some data
INSERT INTO Employees VALUES
    ('/1/', 'Jan Novák', 'Generální ředitel'),
    ('/1/1/', 'Petra Svobodová', 'Obchodní ředitelka'),
    ('/1/1/1/', 'František Vocásek', 'Regionální obchodní ředitel Čechy'),
    ('/1/1/1/1/', 'Martina Tomková', 'Obchodník'),
    ('/1/1/1/2/', 'Pavek Kutil', 'Obchodník'),
    ('/1/1/1/3/', 'Lukáš Havel', 'Obchodník'),
    ('/1/1/2/', 'Ivana Bartošová', 'Regionální obchodní ředitel Morava'),
    ('/1/1/2/1/', 'Tomáš Novotný', 'Obchodník'),
    ('/1/1/2/2/', 'Veronika Kučerová', 'Obchodník'),
    ('/1/1/2/3/', 'David Holý', 'Obchodník'),
    ('/1/1/3/', 'Jiří Kučera', 'Regionální obchodní ředitel Slezsko'),
    ('/1/1/3/1/', 'Anna Pokorná', 'Obchodník'),
    ('/1/1/3/2/', 'Radek Král', 'Obchodník'),
    ('/1/1/3/3/', 'Eva Malá', 'Obchodník'),
    ('/1/2/', 'Pavel Zelenka', 'Finanční ředitel'),
    ('/1/2/1/', 'Kateřina Marešová', 'Finanční analytik'),
    ('/1/2/2/', 'Jakub Němec', 'Účetní'),
    ('/1/2/3/', 'Petra Tichá', 'Kontrolor'),
    ('/1/3/', 'Jana Veselá', 'Personální ředitelka'),
    ('/1/3/1/', 'Michal Kovář', 'Náborář'),
    ('/1/3/2/', 'Martina Benešová', 'Specialista pro vzdělávání'),
    ('/1/3/3/', 'David Růžička', 'Specialista pro rozvoj zaměstnanců'),
    ('/1/4/', 'Roman Svoboda', 'Technický ředitel'),
    ('/1/4/1/', 'Lucie Fialová', 'Inženýr údržby'),
    ('/1/4/2/', 'Petr Černý', 'Inženýr kvality'),
    ('/1/4/3/', 'Klára Horáková', 'Inženýr projektů'),
    ('/1/5/', 'Alena Procházková', 'Marketingový ředitel'),
    ('/1/5/1/', 'Ondřej Dvořák', 'Marketingový specialista'),
    ('/1/5/2/', 'Adéla Hrušková', 'Marketingový analytik'),
    ('/1/5/3/', 'Václav Veselý', 'Koordinátor marketingových kampaní');
GO

-- Get raw data to show how they are stored
SELECT * FROM Employees
GO

-- Get everyone in sales
DECLARE @Superior AS HierarchyId; 
SELECT @Superior = OrgPos FROM Employees WHERE Id = 2
SELECT * FROM Employees WHERE OrgPos.IsDescendantOf(@Superior) = 1
GO

-- Get direct descendants of Sales Manager
DECLARE @Superior AS HierarchyId; 
SELECT @Superior = OrgPos FROM Employees WHERE Id = 2
SELECT * FROM Employees WHERE OrgPos.GetAncestor(1) = @Superior
GO

-- Insert new subordinate
DECLARE @Superior AS HierarchyId
SELECT @Superior = OrgPos FROM Employees WHERE Id = 2
DECLARE @MaxNode AS HierarchyId
SELECT @MaxNode = MAX(OrgPos) FROM Employees WHERE OrgPos.GetAncestor(1) = @Superior

INSERT INTO Employees VALUES (@Superior.GetDescendant(@MaxNode, NULL), 'Štefan Hřebeček', 'Regionální obchodní ředitel pro zahraničí')

SELECT * FROM Employees WHERE OrgPos.IsDescendantOf(@Superior) = 1
GO

-- You CAN create duplicate OrgPos or make tree incomplete by default
-- it can be prevented using UNIQUE and FOREIGN KEY constraints
DECLARE @Superior AS HierarchyId
SELECT @Superior = OrgPos FROM Employees WHERE Id = 2
INSERT INTO Employees VALUES (@Superior, 'XXX', 'YYY');
DELETE FROM Employees WHERE Id = 11

SELECT * FROM Employees ORDER BY OrgPosString
GO

-- Clean up
DROP TABLE Employees
GO