Finding information about organizational hierarchies in SQL Server frequently entails running a query against a table that records hierarchical relationships. One of numerous techniques, such as nested set models, adjacency list models, or recursive Common Table Expressions (CTEs), is frequently used to do this. An outline of each method's methodology is provided here.

1. List of Adjacencies Model
This architecture usually consists of a table with a reference to each row's parent row included in each row. For instance.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

To find the hierarchy, you can use a recursive CTE. Here’s an example of how to retrieve the hierarchy of employees.
WITH EmployeeHierarchy AS (
    -- Anchor member: start with top-level employees (those with no manager)
    SELECT
        EmployeeID,
        Name,
        ManagerID,
        1 AS Level -- Root level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member: join the hierarchy with itself to get child employees
    SELECT
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        eh.Level + 1 AS Level
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh
    ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, ManagerID, EmployeeID;

2. Nested Set Model
In this model, you store hierarchical data using left and right values that define the position of nodes in the hierarchy. Here’s an example table.
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName NVARCHAR(100),
    LeftValue INT,
    RightValue INT
);

To retrieve the hierarchy, you would perform a self-join.
SELECT
    parent.CategoryName AS ParentCategory,
    child.CategoryName AS ChildCategory
FROM Categories parent
INNER JOIN Categories child
ON child.LeftValue BETWEEN parent.LeftValue AND parent.RightValue
WHERE parent.LeftValue < child.LeftValue
ORDER BY parent.LeftValue, child.LeftValue;

3. Path Enumeration Model
In this model, each row stores the path to its root. For example.
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName NVARCHAR(100),
    Path NVARCHAR(MAX)
);

To get the hierarchy, you can query the Path field. Here’s a simple example of getting all descendants of a given node.
DECLARE @CategoryID INT = 1; -- Assuming the root node has CategoryID 1
SELECT *
FROM Categories
WHERE Path LIKE (SELECT Path FROM Categories WHERE CategoryID = @CategoryID) + '%';

Summary
Adjacency List Model: Uses a ManagerID column to establish parent-child relationships. Recursive CTEs are commonly used to traverse the hierarchy.
Nested Set Model: Uses LeftValue and RightValue columns to represent hierarchical relationships. Efficient for read-heavy operations.
Path Enumeration Model: Stores the path to the root, making it easy to query descendants and ancestors.

The choice of model depends on your specific needs and the nature of your hierarchical data.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.