Hierarchical Data in SQL Server: Recursive CTEs, HierarchyID, and Beyond

The Core Problem

Hierarchical data is inherently non-relational. A tree has parent-child relationships, but SQL is built for flat tables. You must choose a representation strategy, and that choice affects every query you write.

Common hierarchies in business applications:

Organizational structures (CEO → VPs → Managers → Employees)
Product taxonomies (Category → Subcategory → SKU)
File systems (Drive → Folders → Subfolders → Files)
Threaded comments (Post → Comment → Reply → Reply-to-Reply)
Bill of Materials (Product → Assemblies → Parts → Subparts)

Strategy 1: Adjacency List (Parent-Child Pointers)

The simplest: each row stores a reference to its parent.

CREATE TABLE Organization (
  EmployeeID INT PRIMARY KEY,
  Name NVARCHAR(100),
  ParentEmployeeID INT NULL,  -- Foreign key to self
  FOREIGN KEY (ParentEmployeeID) REFERENCES Organization(EmployeeID)
);

-- Example data:
-- EmployeeID | Name      | ParentEmployeeID
-- 1          | CEO       | NULL
-- 2          | VP Sales  | 1
-- 3          | VP Eng    | 1
-- 4          | Sales Rep | 2
-- 5          | Engineer  | 3

Query: Get All Descendants

-- Who reports to VP Sales (EmployeeID 2)?
WITH RECURSIVE OrgChart AS (
  -- Base case: start with VP Sales
  SELECT EmployeeID, Name, ParentEmployeeID, 0 AS Level
  FROM Organization
  WHERE EmployeeID = 2

  UNION ALL

  -- Recursive case: find all employees reporting to current level
  SELECT o.EmployeeID, o.Name, o.ParentEmployeeID, oc.Level + 1
  FROM Organization o
  INNER JOIN OrgChart oc
    ON o.ParentEmployeeID = oc.EmployeeID
)
SELECT EmployeeID, Name, Level
FROM OrgChart
ORDER BY Level, Name;

Output: VP Sales (Level 0), Sales Rep (Level 1).

Strengths

✓ Simple schema: one self-referencing foreign key
✓ Easy to insert/update: just set parent ID
✓ Intuitive for small hierarchies

Weaknesses

✗ Queries require recursive CTEs (complex, hard to optimize)
✗ "Get all ancestors" or "get entire subtree" becomes expensive
✗ No built-in depth limit: infinite recursion risk
✗ Performance degrades with deep hierarchies (many joins)

Strategy 2: HierarchyID

SQL Server's native hierarchical data type. It encodes the full path from root to node as a binary value.

CREATE TABLE Organization_HID (
  EmployeeID INT PRIMARY KEY,
  Name NVARCHAR(100),
  HierarchyID HIERARCHYID NOT NULL UNIQUE
);

-- Insert data using GetRoot() and GetDescendant()
INSERT INTO Organization_HID
VALUES
  (1, 'CEO', HIERARCHYID::GetRoot()),
  (2, 'VP Sales', HIERARCHYID::GetRoot().GetDescendant(NULL, NULL)),
  (3, 'VP Eng', HIERARCHYID::GetRoot().GetDescendant(
    (SELECT HierarchyID FROM Organization_HID WHERE EmployeeID = 2),
    NULL
  )),
  (4, 'Sales Rep', (SELECT HierarchyID FROM Organization_HID WHERE EmployeeID = 2)
    .GetDescendant(NULL, NULL));

-- Simpler insert pattern:
INSERT INTO Organization_HID (EmployeeID, Name, HierarchyID)
VALUES (5, 'Engineer', '/1/2/'); -- Encoded path: 1st child of root (CEO), 2nd child of VP Eng

Query: Get All Descendants (Much Simpler)

-- Who reports to VP Sales?
DECLARE @VPSalesHID HIERARCHYID =
  (SELECT HierarchyID FROM Organization_HID WHERE EmployeeID = 2);

SELECT EmployeeID, Name, HierarchyID
FROM Organization_HID
WHERE HierarchyID.IsDescendantOf(@VPSalesHID) = 1;

No recursion needed! HierarchyID's IsDescendantOf() method checks if one node is within another's subtree in a single indexed lookup.

Query: Get All Ancestors

-- Who are all the managers above Sales Rep (EmployeeID 4)?
DECLARE @SalesRepHID HIERARCHYID =
  (SELECT HierarchyID FROM Organization_HID WHERE EmployeeID = 4);

SELECT EmployeeID, Name, HierarchyID
FROM Organization_HID
WHERE @SalesRepHID.IsDescendantOf(HierarchyID) = 1;

Strengths

✓ Blazingly fast ancestor/descendant queries (indexed, no recursion)
✓ No recursive CTE overhead
✓ Built-in path support (navigate by encoded path)
✓ Depth calculation trivial (GetLevel())

Weaknesses

✗ Opaque binary format: hard to debug or inspect
✗ Inserting rows requires careful management of path encoding
✗ Moving subtrees is complex (must recalculate all paths)
✗ SQL Server–specific (not portable)
✗ Reordering siblings requires full path recalculation

Strategy 3: Nested Set (Preorder Traversal)

Assign each node a LeftValue and RightValue representing entry/exit points in a depth-first traversal.

CREATE TABLE Organization_NestedSet (
  EmployeeID INT PRIMARY KEY,
  Name NVARCHAR(100),
  LeftValue INT NOT NULL,
  RightValue INT NOT NULL,
  CHECK (LeftValue < RightValue)
);

-- Example data (visualized as tree traversal):
-- EmployeeID | Name      | LeftValue | RightValue
-- 1          | CEO       | 1         | 10
-- 2          | VP Sales  | 2         | 5
-- 4          | Sales Rep | 3         | 4
-- 3          | VP Eng    | 6         | 9
-- 5          | Engineer  | 7         | 8

Visualization:

        CEO (1---10)
        /          \
   VP Sales       VP Eng
   (2---5)        (6---9)
    /               /
Sales Rep      Engineer
(3---4)        (7---8)

Query: Get All Descendants

-- Who works under VP Sales?
DECLARE @VPSalesLeft INT = (SELECT LeftValue FROM Organization_NestedSet WHERE Name = 'VP Sales');
DECLARE @VPSalesRight INT = (SELECT RightValue FROM Organization_NestedSet WHERE Name = 'VP Sales');

SELECT EmployeeID, Name
FROM Organization_NestedSet
WHERE LeftValue > @VPSalesLeft AND RightValue < @VPSalesRight;

Strengths

✓ Excellent for "get all descendants" (single range query, no recursion)
✓ Fast queries for large hierarchies (index on LeftValue/RightValue)
✓ Depth calculation possible (count ancestors within range)

Weaknesses

✗ Inserting/moving nodes is expensive: must recalculate all LeftValue/RightValue pairs to the right
✗ Concurrent inserts cause contention (locking/deadlock)
✗ Complex to implement correctly
✗ "Get all ancestors" is harder than descendants

Nested Sets are best for: Read-heavy hierarchies that rarely change (taxonomies, catalogs, org structures that only shift quarterly).
Avoid if: You're doing frequent inserts/reorganizations.

Strategy 4: Materialized Path

Store the full path from root to node as a string.

CREATE TABLE Organization_Path (
  EmployeeID INT PRIMARY KEY,
  Name NVARCHAR(100),
  Path NVARCHAR(MAX) NOT NULL  -- e.g., '/1/2/4/' for Sales Rep under VP Sales under CEO
);

-- Example data:
-- EmployeeID | Name      | Path
-- 1          | CEO       | /1/
-- 2          | VP Sales  | /1/2/
-- 4          | Sales Rep | /1/2/4/
-- 3          | VP Eng    | /1/3/
-- 5          | Engineer  | /1/3/5/

Query: Get All Descendants

-- Who reports to VP Sales?
SELECT EmployeeID, Name
FROM Organization_Path
WHERE Path LIKE '/1/2/%';

Strengths

✓ Readable: path is human-interpretable
✓ Fast descendant queries (LIKE or string prefix match)
✓ Easy to debug ("what's the full path of this node?")
✓ Can be indexed (filtered index on path prefix)

Weaknesses

✗ Path grows with depth (deep trees = long strings)
✗ LIKE queries can be slow if path is long or many nodes match
✗ Moving subtrees requires updating all descendant paths
✗ No depth limit built-in (but path length provides practical limit)

Comparison Table

Strategy	Get Descendants	Get Ancestors	Insert Cost	Move Node	Readability
Adjacency List	Slow (recursive)	Slow (recursive)	Fast (add parent ID)	Fast (update parent)	Excellent
HierarchyID	Fast (indexed)	Fast (indexed)	Medium (encode path)	Slow (recalc all)	Poor (binary)
Nested Set	Fast (range)	Medium (range + count)	Slow (recalc all right)	Slow (recalc all)	Medium
Materialized Path	Fast (LIKE)	Medium (parse path)	Fast (concat string)	Medium (update path)	Excellent

Real-World Decision Tree

Organizational Chart (Frequent Moves, Queries Mostly Depth-2)

→ Use Adjacency List. Rarely more than 3 levels deep. Moves are common. Recursion is acceptable.

Product Taxonomy (Read-Heavy, Deep, Rarely Changes)

→ Use HierarchyID or Nested Set. Thousands of nodes. Queries must be fast. Changes are quarterly.

File System or Comments (Mixed Read/Write, Moderate Depth)

→ Use Materialized Path. Readable, fast queries, acceptable insert cost. String paths are intuitive for debugging.

Bill of Materials or Manufacturing (Deep, Complex Assembly, Frequent Changes)

→ Use Adjacency List + Indexed Views. Cache computed subtree sizes using materialized views for fast "count items" queries.

Performance Pitfalls

Pitfall 1: Unbounded Recursion Depth

-- DANGEROUS: No limit on recursion depth
WITH RECURSIVE OrgChart AS (
  SELECT EmployeeID, Name, ParentEmployeeID, 0 AS Level
  FROM Organization
  WHERE EmployeeID = 1

  UNION ALL

  SELECT o.EmployeeID, o.Name, o.ParentEmployeeID, oc.Level + 1
  FROM Organization o
  INNER JOIN OrgChart oc
    ON o.ParentEmployeeID = oc.EmployeeID
  -- Missing: WHERE oc.Level < 100  ← Infinite recursion if cycle exists!
)
SELECT * FROM OrgChart;

⚠ Fix: Always add a MAXRECURSION limit or depth check:
OPTION (MAXRECURSION 100)

Pitfall 2: Not Indexing HierarchyID

-- Create index for descendant queries
CREATE NONCLUSTERED INDEX idx_HierarchyID_IsDescendantOf
ON Organization_HID (HierarchyID);

Pitfall 3: Moving Nodes in Nested Sets Without Recalculation

If you change LeftValue/RightValue manually without recalculating the entire tree, your queries return wrong results silently. Use stored procedures for all modifications.

Hybrid Approach: The Best of Both Worlds

For large, mixed-access hierarchies, combine strategies:

CREATE TABLE Organization_Hybrid (
  EmployeeID INT PRIMARY KEY,
  Name NVARCHAR(100),
  ParentEmployeeID INT NULL,        -- Adjacency list (for inserts)
  HierarchyID HIERARCHYID NOT NULL, -- HierarchyID (for queries)
  Path NVARCHAR(MAX) NOT NULL,      -- Materialized path (for debugging)
  FOREIGN KEY (ParentEmployeeID) REFERENCES Organization_Hybrid(EmployeeID)
);

-- Create indexes on both HierarchyID and Path
CREATE NONCLUSTERED INDEX idx_HierarchyID ON Organization_Hybrid (HierarchyID);
CREATE NONCLUSTERED INDEX idx_Path ON Organization_Hybrid (Path);

-- On insert, calculate all three columns programmatically

Trade-off: Extra storage (three columns instead of one), but queries are blazingly fast and the schema is debuggable.

The Bottom Line

There's no universally "best" hierarchy strategy: only the right choice for your access patterns.

Start with adjacency list for simplicity (unless you know it's read-heavy)
Move to HierarchyID if queries become bottlenecks
Use materialized path if you need readability and moderate speed
Reserve nested sets for read-heavy, rarely-changing hierarchies
Consider hybrid approaches for large, complex structures with mixed access patterns

Profile your queries. Benchmark with realistic data. Don't over-engineer until you have proof the hierarchy is a bottleneck.