Working with Hierarchies in SQL Server

Table of Contents

1. Course Overview
2. Overview of hierarchies in SQL Server

• 2.1 Introduction
• 2.2 Overview of hierarchies in SQL Server
• 2.3 Demo — Create and add a hierarchy with hierarchyid
• 2.4 Compare hierarchyid to other hierarchy models
• 2.5 Demo — Compare hierarchyid to adjacency lists
• 2.6 Best Practices

3. Working with Nodes in a Hierarchy

• 3.1 Introduction
• 3.2 Node properties
• 3.3 Demo — Find descendants by traversing the hierarchy
• 3.4 Demo — Insert nodes into an existing hierarchy
• 3.5 Demo — Move nodes in an existing hierarchy
• 3.6 Demo — Calculate depth and width with recursive CTEs
• 3.7 Best Practices

4. Optimize Performance and Maintain Integrity

• 4.1 Introduction
• 4.2 Demo — Optimize performance with indexes
• 4.3 Demo — Maintain integrity with triggers
• 4.4 Best Practices

5. Demo Files — Complete SQL Code Reference

• 5.1 Module 2 — Demo 2
• 5.2 Module 3 — Demo 3
• 5.3 Module 4 — Demo 4

1. Course Overview

This course, Working with Hierarchies in SQL Server, is led by Pinal Dave, SQL Server expert at sqlauthority.com. Understanding the hierarchyid data type in SQL Server is essential for managing hierarchical or nested data structures, which improves the efficiency and performance of database queries.

What you will learn

• Create and add hierarchy using hierarchyid
• Working with nodes in a hierarchy
• Optimize performance and maintain integrity in a hierarchy

Prerequisites

• Knowledge of SQL Server basics

2. Overview of hierarchies in SQL Server

2.1 Introduction

This module establishes the foundation needed to understand hierarchies in SQL Server. Here is what will be covered:

• Complete introduction to the concept of hierarchy
• Deep dive into hierarchyid data type
• Comparison between hierarchyid approach and alternative data models
• Practical demonstrations
• Best Practices

Required software:

• SQL Server 2022 (previous versions are also compatible for most demos)
• SQL Server Management Studio (SSMS)

2.2 Overview of hierarchies in SQL Server

Defining a hierarchy

Hierarchies represent structural relationships between data elements, often organized in a parent-child fashion, facilitating organization and analysis in databases.

Fundamental structure: nodes and edges

A hierarchy in SQL Server has two essential components:

Structural rules:

• Each node is linked to exactly one parent node
• A node can have zero or more child nodes
• There is a unique case: the root node, which has no parent

Example — Business hierarchy

Consider a simple business hierarchy:

• The CEO is the root node
• Directly under the CEO, we find the department heads
• Each department head supervises their respective team (child nodes)

The hierarchyid data type in SQL Server

In SQL Server, hierarchies are represented by a separate data type: hierarchyid. This type of data:

• Is a system-supplied, variable-length binary format
• Stores values corresponding to nodes in the hierarchical structure
• Facilitates fast and efficient queries on hierarchy-related data
• Comes with a collection of methods for common operations on hierarchical data

Key methods of type hierarchyid

2.3 Demo — Create and add hierarchy with hierarchyid

Step 1 — Setting up the demo environment

The SQLAuthority database is used for all demonstrations. An employees table is created with the following columns:

Initial data structure

The table contains an organizational hierarchy where:

• John is the CEO (ManagerID = NULL) — he is at the top of the hierarchy
• Emily (CTO) and Michael (CRO) report to John
• Sophia and David (Developers) report to Emily
• Jane (Sales) and Rachel (Marketing) report to Michael

Step 2 — Add the HierarchyID column

To create the hierarchyid hierarchy, a recursive CTE is used to calculate the hierarchical path of each employee, then the HierarchyID column is updated with these values:

ALTER TABLE employees ADD HierarchyID hierarchyid;

The column population uses a recursive CTE based on the ManagerID relationship, constructing the paths as strings like /, /1/, /1/1/, etc., which are then cast to hierarchyid.

Step 3 — Query the hierarchy

After the population, we can query the table to obtain enriched information:

SELECT 
    *, 
    HierarchyID.ToString() as HierarchyString,    -- Représentation lisible du chemin
    HierarchyID.GetLevel() as Level,              -- Profondeur dans la hiérarchie
    CASE 
        WHEN HierarchyID.GetLevel() = 0 THEN HierarchyID.ToString() 
        ELSE HierarchyID.GetAncestor(1).ToString() 
    END as Parent,                                 -- Chemin du parent
    CASE 
        WHEN HierarchyID = hierarchyid::GetRoot() THEN 'Yes' 
        ELSE 'No' 
    END as IsRoot                                  -- Est-ce le nœud racine?
FROM employees;

2.4 Compare hierarchyid to other hierarchy models

Several models exist to represent hierarchies in relational databases. Here is a comparison:

Model 1 — Adjacency List

The most common model in relational databases. Each record contains an ID and a parent ID.

Advantages: – Simple to understand and implement

• Easily readable for direct parent-child relationships

Disadvantages:

• Retrieving all descendants requires a recursive query, often slow for large hierarchies
• Performance degrades with hierarchy depth

Model 2 — Nested Set

Uses left and right values ​​for each node, defining a range covering its descendants.

Advantages:

• Quickly discover all descendants or ancestors of a node

Disadvantages:

• Changing hierarchy is expensive — updating left/right values ​​for many nodes may be necessary

Model 3 — hierarchyid (recommended)

Advantages compared to other models:

The hierarchyid type stores a path from the root node to each node, which allows updating the position of a node without affecting other nodes.

2.5 Demo — Compare hierarchyid to adjacency lists

This demo compares the performance between the recursive CTE (adjacency list) approach and the hierarchyid approach to retrieve all employees managed directly or indirectly by the CEO (EmployeeID = 1), using SET STATISTICS IO ON.

Approach 1 — Recursive CTE (Adjacency List)

WITH EmployeeDescendants AS (
    SELECT EmployeeID, EmployeeName, ManagerID, HierarchyID,
           HierarchyID.ToString() as HierarchyString
    FROM employees WHERE EmployeeID = 1
    UNION ALL
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, e.HierarchyID,
           e.HierarchyID.ToString() as HierarchyString
    FROM employees e
    INNER JOIN EmployeeDescendants ed ON e.ManagerID = ed.EmployeeID
)
SELECT * FROM EmployeeDescendants ORDER BY EmployeeID;

IO statistics: Table Worktable — 2 scans, 85 logical reads; table employees — 31 logical reads.

Approach 2 — IsDescendantOf

SELECT EmployeeID, EmployeeName, ManagerID, HierarchyID,
       HierarchyID.ToString() as HierarchyString
FROM employees
WHERE HierarchyID.IsDescendantOf(
    (SELECT HierarchyID FROM employees WHERE EmployeeID = 1)
) = 1
ORDER BY EmployeeID;

Approach 3 — IsDescendantOf with EXISTS subquery

SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, e.HierarchyID,
       e.HierarchyID.ToString() AS HierarchyString
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employees e2
    WHERE e2.EmployeeID = 1 
      AND e.HierarchyID.IsDescendantOf(e2.HierarchyID) = 1
)
ORDER BY EmployeeID;

Approach 4 — IsDescendantOf with CROSS APPLY

SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, e.HierarchyID,
       e.HierarchyID.ToString() AS HierarchyString
FROM employees e
CROSS APPLY (
    SELECT 1 col FROM employees e2 
    WHERE e2.EmployeeID = 1 
      AND e.HierarchyID.IsDescendantOf(e2.HierarchyID) = 1
) ca
WHERE ca.col IS NOT NULL
ORDER BY EmployeeID;

Conclusion: The hierarchyid approach with IsDescendantOf is generally more efficient than recursive CTE, especially on large hierarchies.

2.6 Best Practices

1. Well-Structured Data Modeling — Create data models that define distinct entities and establish unambiguous parent-child relationships. This foundation is essential for maintaining an organized hierarchy.

2. Prefer hierarchyid — Opt for the hierarchyid data type to capitalize on its performance advantages, allowing fast queries and smooth manipulation.

3. Caution with recursive CTEs — When using adjacency list models, be careful with recursive CTEs. Although they are flexible, they can be very expensive.

4. Storage/performance balance — Seek a harmonious balance between storage efficiency and performance by exploiting capabilities of the hierarchyid type. This strategic approach ensures optimal resource utilization while maintaining fast query performance.

5. Documentation and Communication — Effectively document and communicate the hierarchy structure. This is critical when your organization has a large number of hierarchies and many different stakeholders.

3. Working with Nodes in a Hierarchy

3.1 Introduction

Nodes in hierarchyid are fundamental components that establish relationships. It is vital to understand their handling and characteristics. This module covers:

• Nodes as the foundation of any hierarchical structure
• The concepts of depth, breadth, position and descendants
• Practical demonstrations
• Best practices

3.2 Node properties

Node concept

In the hierarchyid system, nodes are related to each other like a parent and child in a family. In an organization, each EmployeeID represents a node. These nodes form relationships with each other, just as employees have relationships in an organization. For example, the manager/employee relationship between EmployeeID 1 and EmployeeID 2 is represented as a parent/child relationship in the hierarchy.

Depth

Depth in a hierarchy refers to the number of levels separating a node from the root node. It is returned by the GetLevel() method.

Width (Breadth)

The width in a hierarchy refers to the number of nodes at each level of the hierarchy. This is the number of direct reports that a manager node has.

Example:

• EmployeeID 1 has 2 direct reports (EmployeeID 2 and 3) → width at level 1 = 2
• EmployeeID 2 and EmployeeID 3 each have 2 direct reports → width at level 2 = 4 in total

Position

The position of a node in the hierarchy refers to the order in which it appears among its siblings (siblings) at the same level. Position is determined by the order in which nodes are inserted or arranged in the hierarchy.

Descendants

A descendant is any node that is below a given node in the hierarchy. The IsDescendantOf() method allows you to quickly check if a node is a descendant of another, directly or indirectly.

3.3 Demo — Find descendants by traversing the hierarchy

This demo demonstrates various techniques for querying hierarchyid data in SQL Server, particularly useful for parent-child relationships.

Get all parent-child relationships

SELECT e1.EmployeeName AS Parent, e2.EmployeeName AS Child
FROM employees e1  
JOIN employees e2 ON e2.HierarchyID.IsDescendantOf(e1.HierarchyID) = 1;

Get all descendants of ‘John’ (entire hierarchy below)

SELECT e1.EmployeeName AS Ancestor, des.EmployeeName AS Descendant
FROM employees e1
JOIN employees des ON des.HierarchyID.IsDescendantOf(e1.HierarchyID) = 1
WHERE e1.EmployeeName = 'John';

Get descendants of ‘John’ at level 2 only

SELECT e1.EmployeeName AS Ancestor, des.EmployeeName AS Descendant
FROM employees e1
JOIN employees des ON des.HierarchyID.IsDescendantOf(e1.HierarchyID) = 1
WHERE e1.EmployeeName = 'John'
AND des.HierarchyID.GetLevel() = 2;

Get direct children of ‘John’

Use GetAncestor(1) to find the immediate parent of each node:

SELECT e1.EmployeeName AS Parent, e2.EmployeeName AS Child
FROM employees e1
JOIN employees e2 ON e2.HierarchyID.GetAncestor(1) = e1.HierarchyID
WHERE e1.EmployeeName = 'John';

Get the level of each node

SELECT EmployeeName, HierarchyID.GetLevel() AS Level
FROM employees;

Get nodes at level 2

SELECT EmployeeName  
FROM employees
WHERE HierarchyID.GetLevel() = 2;

Find the root node

SELECT EmployeeName
FROM employees
WHERE HierarchyID = hierarchyid::GetRoot();

Find all nodes at the same level as a given node (peers/siblings)

SELECT e1.EmployeeName AS Peer, e2.EmployeeName 
FROM employees e1
JOIN employees e2 ON e2.HierarchyID.GetAncestor(1) = e1.HierarchyID.GetAncestor(1)
WHERE e1.EmployeeName = 'Emily';

Get total depth of hierarchy

SELECT MAX(HierarchyID.GetLevel()) AS Depth
FROM employees;

Find leaf nodes (no children)

SELECT EmployeeName
FROM employees e1 
WHERE NOT EXISTS (
    SELECT 1 
    FROM employees e2
    WHERE e2.HierarchyID.GetAncestor(1) = e1.HierarchyID
);

Advanced example — Get the entire management chain for an employee

Recursive CTE approach:

WITH ManagementChain AS (
    SELECT EmployeeID, EmployeeName, ManagerID, HierarchyID
    FROM employees WHERE EmployeeID = 9
    UNION ALL
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, e.HierarchyID
    FROM employees e
    INNER JOIN ManagementChain mc ON e.EmployeeID = mc.ManagerID
)
SELECT * FROM ManagementChain;

Table variable approach with WHILE loop:

DECLARE @ManagementChain TABLE (
    EmployeeID int, EmployeeName varchar(255),
    ManagerID int, HierarchyID hierarchyid, HierarchyString nvarchar(4000)
);

DECLARE @EmployeeHierarchyID hierarchyid;
SELECT @EmployeeHierarchyID = HierarchyID FROM employees WHERE EmployeeID = 9;

WHILE @EmployeeHierarchyID.GetLevel() >= 0
BEGIN
    INSERT INTO @ManagementChain
    SELECT EmployeeID, EmployeeName, ManagerID, HierarchyID, HierarchyID.ToString()
    FROM employees WHERE HierarchyID = @EmployeeHierarchyID;
    -- Remonter dans la hiérarchie
    SET @EmployeeHierarchyID = @EmployeeHierarchyID.GetAncestor(1);
END;

SELECT * FROM @ManagementChain;

3.4 Demo — Insert nodes into an existing hierarchy

This demo explains how to add a new employee to a table that already uses HierarchyID.

Insertion process

Step 1 — Check current hierarchy status:

SELECT *, 
    HierarchyID.ToString() AS HierarchyString, 
    HierarchyID.GetLevel() AS Level,
    CASE 
        WHEN HierarchyID.GetLevel() = 0 THEN HierarchyID.ToString() 
        ELSE HierarchyID.GetAncestor(1).ToString() 
    END AS Parent,
    CASE 
        WHEN HierarchyID = hierarchyid::GetRoot() THEN 'Yes' 
        ELSE 'No' 
    END AS IsRoot
FROM employees ORDER BY Level;

Step 2 — Insert the new employee:

INSERT INTO employees (EmployeeID, EmployeeName, ManagerID, Role)
VALUES (15, 'NewEmployee', 1, 'NewRole');

Step 3 — Temporarily reset the HierarchyID column:

UPDATE employees SET HierarchyID = NULL;

Note: This reset is not always necessary. If we don’t reset all HierarchyID to NULL, it will still work, because the next step uses a CTE that only updates rows with HierarchyID IS NULL.

Step 4 — Repopulate the HierarchyID column with the recursive CTE:

WITH EmployeeHierarchy AS 
(
    SELECT EmployeeID, ManagerID, 
        CAST('/' AS VARCHAR(MAX)) AS HierarchyString
    FROM employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID,
        CAST(eh.HierarchyString + 
            CAST((ROW_NUMBER() OVER (PARTITION BY e.ManagerID ORDER BY e.EmployeeID)) AS VARCHAR(MAX)) + '/' AS VARCHAR(MAX))
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
    WHERE e.HierarchyID IS NULL   -- Condition additionnelle pour ne traiter que les nouvelles lignes
)
UPDATE employees
SET HierarchyID = CAST(eh.HierarchyString AS hierarchyid)
FROM EmployeeHierarchy eh
WHERE employees.EmployeeID = eh.EmployeeID;

Step 5 — Verify that the hierarchy is correct:

SELECT *, 
    HierarchyID.ToString() AS HierarchyString, 
    HierarchyID.GetLevel() AS Level,
    CASE 
        WHEN HierarchyID.GetLevel() = 0 THEN HierarchyID.ToString() 
        ELSE HierarchyID.GetAncestor(1).ToString() 
    END AS Parent,
    CASE 
        WHEN HierarchyID = hierarchyid::GetRoot() THEN 'Yes' 
        ELSE 'No' 
    END AS IsRoot
FROM employees ORDER BY Level;

3.5 Demo — Moving Nodes in an Existing Hierarchy

This demo shows how to move a node from an existing parent to a new parent, using the GetReparentedValue() method.

Scenario

Move employee Emma (formerly under Rachel/Marketing) to the NewEmployee previously created.

Node move code

-- Obtenir le HierarchyID du nouveau manager
DECLARE @NewManagerHierarchyID hierarchyid = 
    (SELECT HierarchyID FROM employees WHERE EmployeeName = 'NewEmployee');

-- Obtenir l'ancien et le nouveau parent pour Emma
DECLARE @OldParentHierarchyID hierarchyid = 
    (SELECT HierarchyID FROM employees WHERE EmployeeName = 'Emma').GetAncestor(1);
DECLARE @NewParentHierarchyID hierarchyid = @NewManagerHierarchyID;

-- Mettre à jour le HierarchyID d'Emma avec GetReparentedValue
UPDATE employees
SET HierarchyID = HierarchyID.GetReparentedValue(@OldParentHierarchyID, @NewParentHierarchyID)
WHERE EmployeeName = 'Emma';

-- Mettre à jour le ManagerID d'Emma pour la cohérence
UPDATE employees
SET ManagerID = (SELECT EmployeeID FROM employees WHERE EmployeeName = 'NewEmployee')
WHERE EmployeeName = 'Emma';

Explanation of GetReparentedValue

The GetReparentedValue(oldRoot, newRoot) method is at the heart of moving nodes:

• It takes the old parent and the new parent as parameters
• It adjusts Emma’s HierarchyID in the hierarchy while maintaining the necessary relationships
• All details of HierarchyString and HierarchyID are updated automatically

Important: The ManagerID column is also updated separately. Although it may seem redundant once hierarchyid is implemented, it is retained for data consistency.

3.6 Demo — Calculate depth and width with recursive CTEs

This demo compares two approaches to calculating hierarchy depth and width, using SET STATISTICS IO ON to measure performance.

Approach 1 — With hierarchyid and GetLevel()

SET STATISTICS IO ON;

SELECT 
    HierarchyID.GetLevel() AS Depth,
    COUNT(*) AS Breadth
FROM employees
GROUP BY HierarchyID.GetLevel()
ORDER BY Depth;

Note: The root node starts at level 0 with hierarchyid.

Approach 2 — With a recursive CTE

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Depth
    FROM employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Depth + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT Depth, COUNT(*) AS Breadth
FROM EmployeeHierarchy
GROUP BY Depth
ORDER BY Depth;

Note: The root node starts at level 1 with a recursive CTE.

Performance comparison

The hierarchyid query only requires reading 3 pages of data, while CTE queries read many more. This clearly shows that the hierarchyid query works faster and is more efficient.

Interview all employees with their depth

With hierarchyid:

SELECT 
    EmployeeID, EmployeeName, 
    HierarchyID.ToString() AS HierarchyString,
    HierarchyID.GetLevel() AS Depth
FROM employees;

With recursive CTE:

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Depth
    FROM employees WHERE ManagerID IS NULL     
    UNION ALL    
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Depth + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

3.7 Best Practices

1. Meticulously plan hierarchy structure — Ensure logical organization and maintain clarity throughout. Good initial design is essential to avoid costly migrations later.

2. Balance depth and width — Achieving a balance between the depth and width of the hierarchy not only improves performance, but also helps understand the data better.

3. Precision in node placement — When placing nodes in the hierarchy, precision is key. Precise positioning of nodes helps to accurately represent the relationships between different entities.

4. Effectively exploit the properties of hierarchyid — Use the built-in methods (IsDescendantOf, GetLevel, GetAncestor, etc.) to manage descendants and ancestors optimally. This optimization technique improves the overall performance of the hierarchy.

4. Optimize Performance and Maintain Integrity

4.1 Introduction

This final module explores two crucial aspects of managing hierarchies in production:

• Indexes — to optimize performance of hierarchical queries
• Triggers — to maintain the integrity of hierarchical data

Role of indexes in hierarchies

Indexes play a central role in improving the performance of hierarchies. By strategically placing indexes on columns that frequently participate in search, filtering or sorting operations, you can significantly speed up data retrieval. Indexes act as a road map, helping the database engine locate data more efficiently, especially in large hierarchies.

Role of triggers in hierarchies

Triggers serve as guardians of data integrity within a hierarchy. They respond quickly to data changes, ensuring the hierarchy remains consistent and accurate. For example, a trigger can prevent the deletion of a node if it has children, thus preserving the hierarchical structure.

4.2 Demo — Optimize performance with indexes

Implementation — Table EmployeeBig (1000+ rows)

For this demonstration, a larger EmployeeBig table is created:

CREATE TABLE EmployeeBig (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(255),
    ManagerID INT,
    Role VARCHAR(255)
);

Initial data (100 employees) is inserted, then multiplied through a loop to reach over 1000 rows. The HierarchyID column is added with a calculated column Level based on the hierarchy functions:

ALTER TABLE EmployeeBig ADD HierarchyID hierarchyid;
ALTER TABLE EmployeeBig ADD Level AS HierarchyID.GetLevel();

Hint: The Level column is a persisted calculated column based on HierarchyID.GetLevel(). This allows indexing directly on Level for filters like WHERE Level = 1.

Query without index

SET STATISTICS IO ON;

SELECT TOP 20 * FROM EmployeeBig;

-- Requête pour les employés de niveau 1
SELECT
    e.EmployeeName,
    e.HierarchyID.GetLevel() AS Level,
    e.HierarchyID.GetAncestor(1).ToString() AS Parent,
    e.HierarchyID.ToString() AS Path
FROM EmployeeBig e
WHERE e.Level = 1
ORDER BY e.EmployeeName;

IO result before index: 1 scan, 12 logical reads.

Creating an optimized non-clustered index

CREATE NONCLUSTERED INDEX idx_levels
ON EmployeeBig (Level) INCLUDE (EmployeeName, HierarchyID);

Explanations:

• The Level column is in the index key because it is used in the WHERE clause
• Columns EmployeeName and HierarchyID are in the INCLUDE clause because they appear in the SELECT clause
• Non-clustered indexes improve performance by allowing the database engine to directly access relevant data without scanning the entire table

Query after index

The same query now benefits from the index and performs significantly fewer logical reads.

Cleaning

DROP INDEX idx_levels ON EmployeeBig;

4.3 Demo — Maintaining integrity with triggers

Context

When working with hierarchyid, it is essential to ensure that one prevents data that could harm the reliability of the database and lead to inconsistent information.

Scenario: EmployeeID 2 is a manager with subordinates, while EmployeeID 13 is not.

Trigger 1 — Prevent deletion of a manager (via ManagerID)

CREATE OR ALTER TRIGGER trg_PreventEmployeeDeletion
ON employees
INSTEAD OF DELETE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM deleted d
        WHERE d.EmployeeID IN (SELECT ManagerID FROM employees)
    )
    BEGIN
        RAISERROR('Cannot delete an employee who is a manager.', 16, 1)
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;

Tests:

• DELETE FROM employees WHERE EmployeeID = 2; → Fail (manager with subordinates)
• DELETE FROM employees WHERE EmployeeID = 13; → Success (no subordinates)

Trigger 2 — Prevent deletion of a manager (via hierarchyid)

CREATE OR ALTER TRIGGER trg_PreventEmployeeDeletionHierarchy
ON employees
INSTEAD OF DELETE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM deleted d
        INNER JOIN employees e ON e.HierarchyID.GetAncestor(1) = d.HierarchyID
    )
    BEGIN
        RAISERROR('Cannot delete an employee who is a manager.', 16, 1)
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;

Key difference: Instead of checking the employees table for ManagerID, this trigger uses GetAncestor(1) to determine if any employees have the deleted employee as a direct parent in the hierarchyid hierarchy. This is an approach more consistent with the hierarchyid model.

Tests:

• DELETE FROM employees WHERE EmployeeID = 2; → Blocked by the trigger
• DELETE FROM employees WHERE EmployeeID = 14; → Success (no direct reports)

Trigger 3 — Verify that a valid manager is assigned (AFTER INSERT/UPDATE)

CREATE OR ALTER TRIGGER trg_EmployeeManagerCheck
ON employees
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        LEFT JOIN employees e ON i.ManagerID = e.EmployeeID
        WHERE e.EmployeeID IS NULL
    )
    BEGIN
        RAISERROR('Invalid manager ID. The manager must be an existing employee.', 16, 1)
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;

Tests:

-- Échoue : ManagerID = NULL et ce n'est pas un CEO autorisé
INSERT INTO employees (EmployeeID, EmployeeName, ManagerID, Role)
VALUES (2002, 'Johnny', NULL, 'NotCEO');

-- Réussit : ManagerID = 1 (John le CEO existe bien)
INSERT INTO employees (EmployeeID, EmployeeName, ManagerID, Role)
VALUES (2002, 'Johnny', 1, 'NotCEO');

4.4 Best practices

1. Using Indexes to Optimize Hierarchy Performance — Indexes play an important role in optimizing hierarchy performance, improving its efficiency and responsiveness. Use indexes as a navigation guide, directing data access in the hierarchy toward speed and accuracy.

2. Use triggers as guardrails — Triggers act as important guardrails preserving hierarchy consistency and ensuring data integrity. Applying predefined rules via triggers helps maintain the overall integrity of the hierarchy.

3. Combine indexes and triggers wisely — For a healthy and efficient hierarchy in production, the combination of well-designed indexes and targeted triggers represents the most effective strategy.

5. Demo Files — Complete SQL Code Reference

5.1 Module 2 — Demo 2

0-Setup.sql — Creating the employees database and table

-- Créer la base de données si elle n'existe pas
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'SQLAuthority')
BEGIN
    CREATE DATABASE SQLAuthority;
END;
GO

USE SQLAuthority;

-- Supprimer la table si elle existe déjà
IF OBJECT_ID('employees', 'U') IS NOT NULL
BEGIN
    DROP TABLE employees;
END

-- Créer la table employees
CREATE TABLE employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(50),
    ManagerID INT,
    Role NVARCHAR(50)
);

-- Insérer les données initiales
INSERT INTO employees (EmployeeID, EmployeeName, ManagerID, Role)
VALUES
    (1, 'John', NULL, 'CEO'),
    (2, 'Emily', 1, 'CTO'),
    (3, 'Michael', 1, 'CRO'),
    (4, 'Sophia', 2, 'Developer'),
    (5, 'David', 2, 'Developer'),
    (6, 'Jane', 3, 'Sales'),
    (7, 'Rachel', 3, 'Marketing');

-- Insérer des lignes additionnelles
INSERT INTO employees (EmployeeID, EmployeeName, ManagerID, Role)
VALUES
    (8, 'Daniel', 4, 'Developer'),
    (9, 'Olivia', 4, 'Designer'),
    (10, 'William', 3, 'Finance'),
    (11, 'Liam', 2, 'Developer'),
    (12, 'Emma', 7, 'Analyst'),
    (13, 'Ava', 6, 'Sales'),
    (14, 'Noah', 2, 'Designer');

SELECT * FROM employees;

1-CreateHierachy.sql — Creating the hierarchy hierarchyid

USE SQLAuthority;
GO

-- Ajouter la colonne HierarchyID
ALTER TABLE employees ADD HierarchyID hierarchyid;
GO

UPDATE employees SET HierarchyID = NULL;
GO

-- Peupler la colonne hierarchyid via CTE récursif
WITH EmployeeHierarchy AS 
(
    -- Membre ancre : employé sans manager (racine)
    SELECT EmployeeID, ManagerID, 
        CAST('/' AS VARCHAR(MAX)) AS HierarchyString
    FROM employees
    WHERE ManagerID IS NULL
    UNION ALL
    -- Membre récursif : employés avec manager
    SELECT e.EmployeeID, e.ManagerID,
        CAST(eh.HierarchyString + CAST((ROW_NUMBER() OVER 
            (PARTITION BY e.ManagerID ORDER BY e.EmployeeID)) AS VARCHAR(MAX)) + '/' AS VARCHAR(MAX))
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
UPDATE employees
SET HierarchyID = CAST(eh.HierarchyString AS hierarchyid)
FROM EmployeeHierarchy eh
WHERE employees.EmployeeID = eh.EmployeeID;
GO

-- Interroger avec les informations de hiérarchie enrichies
SELECT 
    *, 
    HierarchyID.ToString() as HierarchyString, 
    HierarchyID.GetLevel() as Level,
    CASE 
        WHEN HierarchyID.GetLevel() = 0 THEN HierarchyID.ToString() 
        ELSE HierarchyID.GetAncestor(1).ToString() 
    END as Parent,
    CASE 
        WHEN HierarchyID = hierarchyid::GetRoot() THEN 'Yes' 
        ELSE 'No' 
    END as IsRoot
FROM employees;

2-ComparingHierachyid.sql — Comparing hierarchyid vs adjacency list

SET Statistics IO ON;
GO

-- Approche 1 : CTE récursif (Adjacency List)
WITH EmployeeDescendants AS (
    SELECT EmployeeID, EmployeeName, ManagerID, HierarchyID,
           HierarchyID.ToString() as HierarchyString
    FROM employees WHERE EmployeeID = 1
    UNION ALL
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, e.HierarchyID,
           e.HierarchyID.ToString() as HierarchyString
    FROM employees e
    INNER JOIN EmployeeDescendants ed ON e.ManagerID = ed.EmployeeID
)
SELECT * FROM EmployeeDescendants ORDER BY EmployeeID;
GO

-- Approche 2 : IsDescendantOf (hierarchyid)
SELECT EmployeeID, EmployeeName, ManagerID, HierarchyID,
       HierarchyID.ToString() as HierarchyString
FROM employees
WHERE HierarchyID.IsDescendantOf(
    (SELECT HierarchyID FROM employees WHERE EmployeeID = 1)
) = 1
ORDER BY EmployeeID;   
GO

-- Approche 3 : IsDescendantOf avec EXISTS
SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, e.HierarchyID,
       e.HierarchyID.ToString() AS HierarchyString
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employees e2
    WHERE e2.EmployeeID = 1 
      AND e.HierarchyID.IsDescendantOf(e2.HierarchyID) = 1
)
ORDER BY EmployeeID;
GO

-- Approche 4 : IsDescendantOf avec CROSS APPLY
SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, e.HierarchyID,
       e.HierarchyID.ToString() AS HierarchyString
FROM employees e
CROSS APPLY (
    SELECT 1 col FROM employees e2 
    WHERE e2.EmployeeID = 1 
      AND e.HierarchyID.IsDescendantOf(e2.HierarchyID) = 1
) ca
WHERE ca.col IS NOT NULL
ORDER BY EmployeeID;
GO

-- Exemple supplémentaire : Chaîne de management complète pour l'employé 9
WITH ManagementChain AS (
    SELECT EmployeeID, EmployeeName, ManagerID, HierarchyID
    FROM employees WHERE EmployeeID = 9
    UNION ALL
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, e.HierarchyID
    FROM employees e
    INNER JOIN ManagementChain mc ON e.EmployeeID = mc.ManagerID
)
SELECT * FROM ManagementChain;

-- Méthode par variable de table
DECLARE @ManagementChain TABLE (
    EmployeeID int, EmployeeName varchar(255),
    ManagerID int, HierarchyID hierarchyid, HierarchyString nvarchar(4000)
);

DECLARE @EmployeeHierarchyID hierarchyid;
SELECT @EmployeeHierarchyID = HierarchyID FROM employees WHERE EmployeeID = 9;

WHILE @EmployeeHierarchyID.GetLevel() >= 0
BEGIN
    INSERT INTO @ManagementChain
    SELECT EmployeeID, EmployeeName, ManagerID, HierarchyID, HierarchyID.ToString()
    FROM employees WHERE HierarchyID = @EmployeeHierarchyID;
    SET @EmployeeHierarchyID = @EmployeeHierarchyID.GetAncestor(1);
END;

SELECT * FROM @ManagementChain;

5.2 Module 3 — Demo 3

1-TraversingNodes.sql — Traversing nodes in the hierarchy

USE SQLAuthority;
GO

-- Toutes les relations parent-enfant
SELECT e1.EmployeeName AS Parent, e2.EmployeeName AS Child
FROM employees e1  
JOIN employees e2 ON e2.HierarchyID.IsDescendantOf(e1.HierarchyID) = 1;

-- Tous les descendants de 'John'
SELECT e1.EmployeeName AS Ancestor, des.EmployeeName AS Descendant
FROM employees e1
JOIN employees des ON des.HierarchyID.IsDescendantOf(e1.HierarchyID) = 1
WHERE e1.EmployeeName = 'John';

-- Descendants de 'John' au niveau 2 uniquement
SELECT e1.EmployeeName AS Ancestor, des.EmployeeName AS Descendant
FROM employees e1
JOIN employees des ON des.HierarchyID.IsDescendantOf(e1.HierarchyID) = 1
WHERE e1.EmployeeName = 'John'
AND des.HierarchyID.GetLevel() = 2;

-- Tous les descendants de 'John' à tout niveau
SELECT e1.EmployeeName AS Ancestor, des.EmployeeName AS Descendant  
FROM employees e1
JOIN employees des ON des.HierarchyID.IsDescendantOf(e1.HierarchyID) = 1
WHERE e1.EmployeeName = 'John';

-- Enfants directs de 'John'
SELECT e1.EmployeeName AS Parent, e2.EmployeeName AS Child
FROM employees e1
JOIN employees e2 ON e2.HierarchyID.GetAncestor(1) = e1.HierarchyID
WHERE e1.EmployeeName = 'John';

-- Niveau de chaque nœud
SELECT EmployeeName, HierarchyID.GetLevel() AS Level
FROM employees;

-- Nœuds au niveau 2
SELECT EmployeeName FROM employees
WHERE HierarchyID.GetLevel() = 2;

-- Nœud racine
SELECT EmployeeName FROM employees
WHERE HierarchyID = hierarchyid::GetRoot();

-- Pairs (siblings) au même niveau qu'Emily
SELECT e1.EmployeeName AS Peer, e2.EmployeeName 
FROM employees e1
JOIN employees e2 ON e2.HierarchyID.GetAncestor(1) = e1.HierarchyID.GetAncestor(1)
WHERE e1.EmployeeName = 'Emily';

-- Profondeur totale de la hiérarchie
SELECT MAX(HierarchyID.GetLevel()) AS Depth FROM employees;

-- Nœuds feuilles (sans enfants)
SELECT EmployeeName FROM employees e1 
WHERE NOT EXISTS (
    SELECT 1 FROM employees e2
    WHERE e2.HierarchyID.GetAncestor(1) = e1.HierarchyID
);

2-InsertingNodes.sql — Insert nodes into hierarchy

USE SQLAuthority;
GO

-- Vérifier l'état de la hiérarchie avant insertion
SELECT *, 
    HierarchyID.ToString() AS HierarchyString, 
    HierarchyID.GetLevel() AS Level,
    CASE 
        WHEN HierarchyID.GetLevel() = 0 THEN HierarchyID.ToString() 
        ELSE HierarchyID.GetAncestor(1).ToString() 
    END AS Parent,
    CASE 
        WHEN HierarchyID = hierarchyid::GetRoot() THEN 'Yes' 
        ELSE 'No' 
    END AS IsRoot
FROM employees ORDER BY Level;
GO

-- Insérer un nouvel employé sous John
INSERT INTO employees (EmployeeID, EmployeeName, ManagerID, Role)
VALUES (15, 'NewEmployee', 1, 'NewRole');
GO

-- Vérifier après insertion
SELECT * FROM employees ORDER BY HierarchyID.GetLevel();

-- Réinitialiser la colonne HierarchyID (optionnel)
UPDATE employees SET HierarchyID = NULL;
GO

-- Repeupler hierarchyid (ne traite que les lignes avec HierarchyID IS NULL)
WITH EmployeeHierarchy AS 
(
    SELECT EmployeeID, ManagerID, 
        CAST('/' AS VARCHAR(MAX)) AS HierarchyString
    FROM employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID,
        CAST(eh.HierarchyString + 
            CAST((ROW_NUMBER() OVER (PARTITION BY e.ManagerID ORDER BY e.EmployeeID)) AS VARCHAR(MAX)) + '/' AS VARCHAR(MAX))
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
    WHERE e.HierarchyID IS NULL
)
UPDATE employees
SET HierarchyID = CAST(eh.HierarchyString AS hierarchyid)
FROM EmployeeHierarchy eh
WHERE employees.EmployeeID = eh.EmployeeID;
GO

-- Vérification finale
SELECT *, 
    HierarchyID.ToString() AS HierarchyString, 
    HierarchyID.GetLevel() AS Level,
    CASE 
        WHEN HierarchyID.GetLevel() = 0 THEN HierarchyID.ToString() 
        ELSE HierarchyID.GetAncestor(1).ToString() 
    END AS Parent,
    CASE 
        WHEN HierarchyID = hierarchyid::GetRoot() THEN 'Yes' 
        ELSE 'No' 
    END AS IsRoot
FROM employees ORDER BY Level;
GO

3-MovingNodes.sql — Move nodes in the hierarchy

USE SQLAuthority;
GO

-- État initial
SELECT *, 
    HierarchyID.ToString() AS HierarchyString, 
    HierarchyID.GetLevel() AS Level,
    CASE 
        WHEN HierarchyID.GetLevel() = 0 THEN HierarchyID.ToString() 
        ELSE HierarchyID.GetAncestor(1).ToString() 
    END AS Parent,
    CASE 
        WHEN HierarchyID = hierarchyid::GetRoot() THEN 'Yes' 
        ELSE 'No' 
    END AS IsRoot
FROM employees ORDER BY Level;
GO

-- Déplacer Emma vers NewEmployee en tant que nouveau manager
DECLARE @NewManagerHierarchyID hierarchyid = 
    (SELECT HierarchyID FROM employees WHERE EmployeeName = 'NewEmployee');

DECLARE @OldParentHierarchyID hierarchyid = 
    (SELECT HierarchyID FROM employees WHERE EmployeeName = 'Emma').GetAncestor(1);
DECLARE @NewParentHierarchyID hierarchyid = @NewManagerHierarchyID;

-- Mettre à jour HierarchyID avec GetReparentedValue
UPDATE employees
SET HierarchyID = HierarchyID.GetReparentedValue(@OldParentHierarchyID, @NewParentHierarchyID)
WHERE EmployeeName = 'Emma';

-- Mettre à jour ManagerID pour la cohérence
UPDATE employees
SET ManagerID = (SELECT EmployeeID FROM employees WHERE EmployeeName = 'NewEmployee')
WHERE EmployeeName = 'Emma';
GO

-- Vérification après déplacement
SELECT *, 
    HierarchyID.ToString() AS HierarchyString, 
    HierarchyID.GetLevel() AS Level,
    CASE 
        WHEN HierarchyID.GetLevel() = 0 THEN HierarchyID.ToString() 
        ELSE HierarchyID.GetAncestor(1).ToString() 
    END AS Parent,
    CASE 
        WHEN HierarchyID = hierarchyid::GetRoot() THEN 'Yes' 
        ELSE 'No' 
    END AS IsRoot
FROM employees ORDER BY Level;
GO

4-DepthBreadth.sql — Depth and width calculation

USE SQLAuthority;
GO

SET STATISTICS IO ON;

-- Approche 1 : hierarchyid (nœud racine = niveau 0)
SELECT 
    HierarchyID.GetLevel() AS Depth,
    COUNT(*) AS Breadth
FROM employees
GROUP BY HierarchyID.GetLevel()
ORDER BY Depth;

-- Approche 2 : CTE récursif (nœud racine = niveau 1)
WITH EmployeeHierarchy AS (
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Depth
    FROM employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Depth + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT Depth, COUNT(*) AS Breadth
FROM EmployeeHierarchy
GROUP BY Depth
ORDER BY Depth;

-- Détails de chaque employé avec sa profondeur (hierarchyid)
SELECT 
    EmployeeID, EmployeeName, 
    HierarchyID.ToString() AS HierarchyString,
    HierarchyID.GetLevel() AS Depth
FROM employees;

-- Détails de chaque employé avec sa profondeur (CTE récursif)
WITH EmployeeHierarchy AS (
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Depth
    FROM employees WHERE ManagerID IS NULL     
    UNION ALL    
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Depth + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

5.3 Module 4 — Demo 4

0-EmployeeBigSetup.sql — Creating the large table EmployeeBig

USE SQLAuthority;
GO

IF OBJECT_ID('EmployeeBig', 'U') IS NOT NULL
    DROP TABLE EmployeeBig;

CREATE TABLE EmployeeBig (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(255),
    ManagerID INT,
    Role VARCHAR(255)
);

-- Insertion des 100 premières lignes (données initiales)
INSERT INTO EmployeeBig (EmployeeID, EmployeeName, ManagerID, Role)
VALUES
(1, 'John', NULL, 'CEO'),
(2, 'Emily', 1, 'CTO'),
(3, 'Michael', 1, 'CRO'),
-- ... (100 employés au total)
(100, 'Samuel', 5, 'Developer');
GO

-- Multiplication des données via une boucle (pour atteindre 1000+ lignes)
DECLARE @i INT = 0;
WHILE @i < 10
BEGIN
    SET @i = @i + 1;
    INSERT INTO EmployeeBig (EmployeeID, EmployeeName, ManagerID, Role)
    SELECT TOP 100
        EmployeeID + @i * 100,
        EmployeeName,
        ManagerID,
        Role
    FROM EmployeeBig ORDER BY EmployeeID;
END;

-- Ajouter la colonne HierarchyID
ALTER TABLE EmployeeBig ADD HierarchyID hierarchyid;
GO

UPDATE EmployeeBig SET HierarchyID = NULL;
GO

-- Ajouter la colonne calculée Level
ALTER TABLE EmployeeBig ADD Level AS HierarchyID.GetLevel();
GO

-- Peupler hierarchyid via CTE récursif
WITH EmployeeHierarchy AS 
(
    SELECT EmployeeID, ManagerID, 
        CAST('/' AS VARCHAR(MAX)) AS HierarchyString
    FROM EmployeeBig WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID,
        CAST(eh.HierarchyString + 
            CAST((ROW_NUMBER() OVER (PARTITION BY e.ManagerID ORDER BY e.EmployeeID)) AS VARCHAR(MAX)) + '/' AS VARCHAR(MAX))
    FROM EmployeeBig e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
UPDATE EmployeeBig
SET HierarchyID = CAST(eh.HierarchyString AS hierarchyid)
FROM EmployeeHierarchy eh
WHERE EmployeeBig.EmployeeID = eh.EmployeeID;
GO

1-IndexPerformance.sql — Optimizing with indexes

USE SQLAuthority;
GO

SET STATISTICS IO ON;
GO

SELECT TOP 20 * FROM EmployeeBig;

-- Requête pour les employés de niveau 1 (AVANT index)
SELECT
    e.EmployeeName,
    e.HierarchyID.GetLevel() AS Level,
    e.HierarchyID.GetAncestor(1).ToString() AS Parent,
    e.HierarchyID.ToString() AS Path
FROM EmployeeBig e
WHERE e.Level = 1
ORDER BY e.EmployeeName;
-- Résultat : 1 scan, 12 lectures logiques

-- Créer un index non-clustérisé sur Level
CREATE NONCLUSTERED INDEX idx_levels
ON EmployeeBig (Level) INCLUDE (EmployeeName, HierarchyID);
GO

-- Même requête APRÈS la création de l'index
SELECT
    e.EmployeeName,
    e.HierarchyID.GetLevel() AS Level,
    e.HierarchyID.GetAncestor(1).ToString() AS Parent,
    e.HierarchyID.ToString() AS Path
FROM EmployeeBig e
WHERE e.Level = 1
ORDER BY e.EmployeeName;
-- Résultat : lectures logiques significativement réduites

-- Nettoyage
DROP INDEX idx_levels ON EmployeeBig;

2-TriggerIntegrity.sql — Maintaining integrity with triggers

USE SQLAuthority;
GO

-- Vérifier l'état initial
SELECT *, 
    HierarchyID.ToString() AS HierarchyString, 
    HierarchyID.GetLevel() AS Level,
    CASE 
        WHEN HierarchyID.GetLevel() = 0 THEN HierarchyID.ToString() 
        ELSE HierarchyID.GetAncestor(1).ToString() 
    END AS Parent,
    CASE 
        WHEN HierarchyID = hierarchyid::GetRoot() THEN 'Yes' 
        ELSE 'No' 
    END AS IsRoot
FROM employees;
GO

-- Trigger 1 : INSTEAD OF DELETE basé sur ManagerID
CREATE OR ALTER TRIGGER trg_PreventEmployeeDeletion
ON employees
INSTEAD OF DELETE
AS
BEGIN
    IF EXISTS (
        SELECT * FROM deleted d
        WHERE d.EmployeeID IN (SELECT ManagerID FROM employees)
    )
    BEGIN
        RAISERROR('Cannot delete an employee who is a manager.', 16, 1)
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;
GO

DELETE FROM employees WHERE EmployeeID = 2;   -- Échoue (manager)
DELETE FROM employees WHERE EmployeeID = 13;  -- Réussit
GO

DROP TRIGGER trg_PreventEmployeeDeletion;
GO

-- Trigger 2 : INSTEAD OF DELETE basé sur hierarchyid
CREATE OR ALTER TRIGGER trg_PreventEmployeeDeletionHierarchy
ON employees
INSTEAD OF DELETE
AS
BEGIN
    IF EXISTS (
        SELECT * FROM deleted d
        INNER JOIN employees e ON e.HierarchyID.GetAncestor(1) = d.HierarchyID
    )
    BEGIN
        RAISERROR('Cannot delete an employee who is a manager.', 16, 1)
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;
GO

DELETE FROM employees WHERE EmployeeID = 2;   -- Bloqué par le trigger
DELETE FROM employees WHERE EmployeeID = 14;  -- Réussit
GO

-- Trigger 3 : AFTER INSERT, UPDATE — Vérifier la validité du manager
CREATE OR ALTER TRIGGER trg_EmployeeManagerCheck
ON employees
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT * FROM inserted i
        LEFT JOIN employees e ON i.ManagerID = e.EmployeeID
        WHERE e.EmployeeID IS NULL
    )
    BEGIN
        RAISERROR('Invalid manager ID. The manager must be an existing employee.', 16, 1)
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;
GO

-- Test 1 : Échoue (ManagerID = NULL non autorisé pour un non-CEO)
INSERT INTO employees (EmployeeID, EmployeeName, ManagerID, Role)
VALUES (2002, 'Johnny', NULL, 'NotCEO');

-- Test 2 : Réussit (ManagerID = 1, John existe)
INSERT INTO employees (EmployeeID, EmployeeName, ManagerID, Role)
VALUES (2002, 'Johnny', 1, 'NotCEO');
GO

6. Summary of key concepts

Search Terms

hierarchies · sql · server · microsoft · databases · hierarchy · hierarchyid · nodes · get · approach · node · depth · adjacency · descendants · find · level · index · indexes · insert · integrity · isdescendantof · john · list · manager