Building an Org Chart Query With hierarchyid

Finding everyone beneath a manager should not require rebuilding the family tree each time. The hierarchyid type stores a node's position in that tree and provides methods for descendants, levels, and moves. It works best when your organization has a clear single parent structure.

Keep Employee Identity Separate From Position

An employee identifier is stable identity. A hierarchyid value is a position in a tree. Moving a team changes positions while employees remain the same people. Store both. The data type does not automatically enforce parent existence, one root, or the absence of duplicates. Ordinary keys and your write rules still govern those requirements.

I ask whether each employee has exactly one manager in the chart being modeled. Does your chart permit more than one current manager for an employee? Matrix reporting, dotted line relationships, and historical assignments need a broader model. Do not squeeze several parents into one path. Use a separate relationship table when the business permits multiple parents. The examples represent one current, fictional reporting tree with a single root.

Turn Manager IDs Into hierarchyid Paths

Begin with an adjacency table, the familiar employee and manager structure. Assign each sibling a deterministic ordinal ordered by employee ID. A recursive CTE appends those ordinals to the parent's path, then hierarchyid::Parse converts the path. The unique clustered index orders by tree position, while the nonclustered primary key retains employee lookup.

CREATE TABLE #Managers
    (EmployeeID int NOT NULL PRIMARY KEY,ManagerID int NULL,EmployeeName nvarchar(80));
INSERT #Managers VALUES
    (1,NULL,N'Avery'),(2,1,N'Blake'),(3,1,N'Casey'),(4,2,N'Drew'),(5,2,N'Ellis');
CREATE TABLE dbo.OrgChart
(
    EmployeeID int NOT NULL CONSTRAINT PK_OrgChart PRIMARY KEY NONCLUSTERED,
    EmployeeName nvarchar(80) NOT NULL,
    OrgNode hierarchyid NOT NULL,
    OrgLevel AS OrgNode.GetLevel() PERSISTED
);
CREATE UNIQUE CLUSTERED INDEX CX_OrgChart_OrgNode ON dbo.OrgChart(OrgNode);
WITH siblings AS
(
    SELECT EmployeeID,ManagerID,EmployeeName,
           ROW_NUMBER() OVER(PARTITION BY ManagerID ORDER BY EmployeeID) AS SiblingNumber
    FROM #Managers
), tree AS
(
    SELECT EmployeeID,EmployeeName,CAST('/' AS varchar(4000)) AS OrgPath
    FROM siblings WHERE ManagerID IS NULL
    UNION ALL
    SELECT c.EmployeeID,c.EmployeeName,
           CAST(p.OrgPath+CONVERT(varchar(20),c.SiblingNumber)+'/' AS varchar(4000))
    FROM tree AS p JOIN siblings AS c ON c.ManagerID=p.EmployeeID
)
INSERT dbo.OrgChart(EmployeeID,EmployeeName,OrgNode)
SELECT EmployeeID,EmployeeName,hierarchyid::Parse(OrgPath)
FROM tree OPTION(MAXRECURSION 100);

Validate the source before loading a real chart. Missing managers, cycles, and disconnected components can leave rows outside a root traversal. Multiple roots would collide at the root position in this design. The explicit recursion and path length limits are safeguards, not support for unlimited depth. Reject malformed input rather than publishing a partial chart. The persisted computed column needs QUOTED_IDENTIFIER ON, which SSMS sets by default; in sqlcmd, add the -I switch.

Check That the Load Reached Everyone

Run these checks in the same session as the source table. The first returns source employees absent from the result. The second makes stored paths and levels readable. GetLevel returns zero for the root, then increases by one at each parent step. A path's printable form is convenient for diagnosis, but application code should use the actual type.

SELECT m.EmployeeID,m.ManagerID
FROM #Managers AS m
LEFT JOIN dbo.OrgChart AS o ON o.EmployeeID=m.EmployeeID
WHERE o.EmployeeID IS NULL;
SELECT EmployeeID,EmployeeName,OrgNode.ToString() AS OrgPath,
       OrgNode.GetLevel() AS TreeLevel
FROM dbo.OrgChart ORDER BY OrgNode;

I reconcile the source population before trusting descendant results. A missing branch makes a query look fast and complete while answering the wrong question. For a production conversion, perform the validated load inside a deliberate transaction and compare the expected identities. Preserve the old structure until the new one and its write paths pass the application's checks.

Return a Manager's Entire Team

IsDescendantOf includes the node itself. Exclude equality when the caller wants only reports. Use GetAncestor to find immediate reports. A stored hierarchyid is ordered in depth first sequence, so ordering by OrgNode places a subtree together. Return EmployeeID with the display fields, since names alone are not unique identifiers.

DECLARE @manager hierarchyid;
SELECT @manager=OrgNode FROM dbo.OrgChart WHERE EmployeeID=2;
SELECT EmployeeID,EmployeeName,OrgNode.ToString() AS OrgPath,OrgLevel
FROM dbo.OrgChart
WHERE OrgNode.IsDescendantOf(@manager)=1 AND OrgNode<>@manager
ORDER BY OrgNode;
SELECT EmployeeID,EmployeeName
FROM dbo.OrgChart WHERE OrgNode.GetAncestor(1)=@manager;

Validate a supplied manager ID before querying. A missing manager should produce a helpful application error instead of an unexplained empty team. Decide whether inactive employees belong in traversal or only in display. Filtering intermediate relationships changes the tree meaning differently from hiding returned names. State that policy before adding active flags to every query.

Move the Whole Branch Together

GetReparentedValue replaces the old root prefix with a new root prefix. Apply it to every descendant, including the moving manager. The sample takes an exclusive table lock during the short move to serialize tree edits. That is deliberately simple for a small demonstration. Large trees need a tested, narrower locking and allocation protocol.

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    DECLARE @old hierarchyid,@parent hierarchyid,@last hierarchyid,@new hierarchyid;
    SELECT @old=OrgNode FROM dbo.OrgChart WITH(TABLOCKX,HOLDLOCK) WHERE EmployeeID=2;
    SELECT @parent=OrgNode FROM dbo.OrgChart WHERE EmployeeID=3;
    IF @old IS NULL OR @parent IS NULL THROW 50001,'Employee not found.',1;
    IF @parent.IsDescendantOf(@old)=1 THROW 50002,'Move would create a cycle.',1;
    SELECT @last=MAX(OrgNode) FROM dbo.OrgChart WHERE OrgNode.GetAncestor(1)=@parent;
    SET @new=@parent.GetDescendant(@last,NULL);
    UPDATE dbo.OrgChart
    SET OrgNode=OrgNode.GetReparentedValue(@old,@new)
    WHERE OrgNode.IsDescendantOf(@old)=1;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT>0 ROLLBACK;
    THROW;
END CATCH;

The move rejects a destination inside the moving subtree. Otherwise you would put a manager underneath that manager's own report. Allocate the new child position while holding the same write protection as the update. Unique path enforcement catches collisions, but it does not coordinate concurrent planners by itself. An org chart cannot fix a reporting loop with a nicer drawing.

Add a Breadth First Index Beside the hierarchyid Key

The clustered OrgNode index supports depth first locality. A second index on level then path supports queries focused on a whole tier. The computed level updates when a node moves. Choose indexes based on actual questions, because each subtree update must maintain every index containing the changed position or computed level.

CREATE INDEX IX_OrgChart_Level_Node ON dbo.OrgChart(OrgLevel,OrgNode)
INCLUDE(EmployeeName);
SELECT EmployeeID,EmployeeName,OrgNode.ToString() AS OrgPath
FROM dbo.OrgChart WHERE OrgLevel=1 ORDER BY OrgNode;

Compare actual plans, reads, and update work on your own chart. Do not claim every descendant query becomes a seek merely because the type supports hierarchical ordering. Predicates, estimates, and indexing still matter. Save representative manager queries from both shallow and deep branches, then compare the access paths with the workload's frequency and change rate.

Compare hierarchyid With the Original Recursive Query

An adjacency table remains easy to understand and cheap to update for a single employee's manager. A recursive CTE follows its parent references for descendants. This query uses the original sample table, which intentionally still represents the pre-move chart. Rebuild or update that source before expecting both models to describe the same post-move organization.

WITH reports AS
(
    SELECT EmployeeID,ManagerID,EmployeeName,0 AS RelativeLevel
    FROM #Managers WHERE EmployeeID=2
    UNION ALL
    SELECT c.EmployeeID,c.ManagerID,c.EmployeeName,p.RelativeLevel+1
    FROM #Managers AS c JOIN reports AS p ON c.ManagerID=p.EmployeeID
)
SELECT EmployeeID,EmployeeName,RelativeLevel
FROM reports OPTION(MAXRECURSION 100);

Frequent subtree reads favor the path model. Frequent large branch moves increase its write cost. Keep the comparison fair by using the same population, rules, and indexes. The type simplifies tree questions, while an adjacency model is still a valid choice when its joins and recursion serve the application clearly.

Make Tree Maintenance Part of the Design

Choose one authoritative structure and one controlled write path. Keeping manager IDs and paths together without coordinated updates creates two disagreeing charts. Test insertions, sibling allocation, subtree moves, root protections, and invalid source data. Ask whether historical reporting needs a separate effective dated organization rather than overwriting today's path.

I review move behavior before adopting the model, because reads are the easy part to demonstrate. A reliable chart needs stable employee identity and deliberate position changes. When those rules are explicit, hierarchyid gives you compact paths and useful methods without forcing every reader to assemble the same parent chain again.

Related reading on this blog: Making Recursive Parent-Child Queries Efficient and Introduction to Hierarchical Query using a Recursive CTE: A Primer.

A hierarchy path is not employee identity, it is a stored position in a managed tree.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.