Finding every descendant gets simpler when a row already stores its route from the root. A materialized path makes that route explicit.
Make the Materialized Path Format Unambiguous
A string such as /1/4/9/ identifies a route through node identifiers. Leading and trailing separators are part of the format. They prevent node four from being confused with node forty.
Use stable numeric identifiers rather than names inside the path. Names change, include punctuation, and need escaping in LIKE patterns. Numeric segments keep this demonstration's wildcard handling simple and predictable.
I define the root depth before writing queries. I also choose a maximum supported path length before creating its index. Those choices keep later assumptions visible during review.
Here, root nodes have depth zero and their own identifier in the path. ParentId separately records the immediate parent. Keeping both representations means updates must preserve their agreement.
CREATE TABLE dbo.PathNode
(
NodeId int NOT NULL PRIMARY KEY CHECK (NodeId > 0),
ParentId int NULL REFERENCES dbo.PathNode(NodeId),
NodeName nvarchar(60) NOT NULL,
NodePath varchar(500) COLLATE Latin1_General_100_BIN2 NOT NULL,
CHECK (LEFT(NodePath, 1) = '/' AND RIGHT(NodePath, 1) = '/'),
CHECK (NodePath NOT LIKE '%[^0-9/]%')
);
CREATE UNIQUE INDEX UX_PathNode_Path
ON dbo.PathNode(NodePath);
INSERT dbo.PathNode VALUES (1, NULL, N'Root', '/1/');
INSERT dbo.PathNode(NodeId, ParentId, NodeName, NodePath)
SELECT 4, NodeId, N'Branch', NodePath + '4/'
FROM dbo.PathNode WHERE NodeId = 1;
INSERT dbo.PathNode(NodeId, ParentId, NodeName, NodePath)
SELECT 9, NodeId, N'Leaf', NodePath + '9/'
FROM dbo.PathNode WHERE NodeId = 4;
INSERT dbo.PathNode(NodeId, ParentId, NodeName, NodePath)
SELECT 12, NodeId, N'Other branch', NodePath + '12/'
FROM dbo.PathNode WHERE NodeId = 1;Compute New Paths From the Parent
Each child appends its identifier and a separator to the stored parent path. The sample inserts demonstrate that construction directly. A production insertion interface should reject a missing parent instead of silently inserting nothing.
The format checks do not prove the entire hierarchy is valid. They permit malformed segment combinations unless another interface prevents them. Restrict direct writes and enforce complete path rules in the approved mutation procedure.
The foreign key prevents a missing recorded parent. It does not prove that NodePath contains that parent's route. Audit those two representations together when importing or repairing hierarchy data.
Path length is also a business limit, not merely a storage detail. Long identifiers and deep trees consume the available bytes. Reject an oversized route before an assignment can truncate the constructed string.
Read Materialized Path Descendants With a Prefix
The path index can support a prefix search because the pattern starts with a known route. The wildcard appears at the end. A leading wildcard would describe a different search with different index opportunities.
The prefix includes its trailing separator. The predicate therefore matches complete ancestor segments. Excluding the node identifier itself distinguishes descendants from a subtree that includes its root.
DECLARE @Path varchar(500);
SELECT @Path = NodePath FROM dbo.PathNode WHERE NodeId = 4;
IF @Path IS NULL THROW 50001, 'Requested node does not exist.', 1;
SELECT NodeId, ParentId, NodeName, NodePath,
LEN(NodePath) - LEN(REPLACE(NodePath, '/', '')) - 2 AS Depth
FROM dbo.PathNode
WHERE NodePath LIKE @Path + '%'
AND NodeId <> 4
ORDER BY NodePath;The depth expression counts separators and subtracts two. A root route has two separators and therefore depth zero. Each added identifier contributes one more separator and one more level. For node 4, the query returns only the Leaf, at depth two.
The index gives the optimizer an access option rather than a guaranteed seek. Table size, selected columns, statistics, and parameter estimates influence its choice. Inspect the actual plan before describing performance on your data.
Move the Whole Subtree Atomically
Moving a materialized path branch requires changing every stored descendant prefix. Updating only the parent row leaves inconsistent routes beneath it. The immediate ParentId changes only for the moved subtree root.
The new parent cannot be the moved node or one of its descendants. Such a move would create a cycle. Prefix comparison can reject that relationship before any path update occurs.
The following example deliberately takes a table lock for its small test hierarchy. That serializes changes during validation and update. Large production hierarchies need a reviewed locking strategy rather than blindly copying this coarse lock.
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
THROW 50000, 'Run this move outside another transaction.', 1;
DECLARE @NodeId int = 4, @NewParentId int = 12;
DECLARE @OldPath varchar(500), @ParentPath varchar(500);
DECLARE @NewPath varchar(8000);
BEGIN TRY
BEGIN TRANSACTION;
SELECT @OldPath = NodePath
FROM dbo.PathNode WITH (TABLOCKX, HOLDLOCK)
WHERE NodeId = @NodeId;
SELECT @ParentPath = NodePath
FROM dbo.PathNode WHERE NodeId = @NewParentId;
IF @OldPath IS NULL OR @ParentPath IS NULL
THROW 50002, 'Node or new parent does not exist.', 1;
IF @ParentPath LIKE @OldPath + '%'
THROW 50003, 'Moving beneath this parent creates a cycle.', 1;
SET @NewPath = @ParentPath + CONVERT(varchar(11), @NodeId) + '/';
IF EXISTS
(
SELECT 1 FROM dbo.PathNode
WHERE NodePath LIKE @OldPath + '%'
AND LEN(@NewPath) + LEN(NodePath) - LEN(@OldPath) > 500
)
THROW 50004, 'The moved subtree exceeds the path limit.', 1;
UPDATE dbo.PathNode
SET NodePath = @NewPath + SUBSTRING(NodePath, LEN(@OldPath) + 1, 500),
ParentId = CASE WHEN NodeId = @NodeId
THEN @NewParentId ELSE ParentId END
WHERE NodePath LIKE @OldPath + '%';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
SELECT NodeId, ParentId, NodePath FROM dbo.PathNode ORDER BY NodeId;Account for Materialized Path Move Costs
A branch move writes all affected paths and updates their index entries. A large subtree can therefore produce substantial logging and blocking. Frequent reorganizations deserve realistic mutation testing before choosing this representation.
A single transaction gives the move atomic database behavior. It does not eliminate lock duration or deadlock possibilities in a broader application. Keep other mutation paths consistent with the chosen serialization strategy.
The sample assumes valid paths before the move begins. Corrupt input can defeat otherwise sensible prefix logic. Run integrity checks before using the operation as part of an existing-data migration.
On SQL Server 2025, the sample move rewrote node 4 to /1/12/4/ and node 9 to /1/12/4/9/. Test descendant membership before and after a move in isolation. Include a rejected cycle and an overlength destination in that test set.
Compare the Alternatives Briefly
An adjacency list stores just the immediate parent reference. A subtree query usually needs recursive traversal or another maintained structure. Moving a node changes its parent reference rather than rewriting every descendant string.
The hierarchyid data type provides a compact hierarchical position with dedicated methods and indexing support. It still needs application rules for valid relationships and moves. Its presence does not automatically enforce an entire tree contract.
String paths are readable and easy to inspect in ordinary query results. That readability comes with duplicated ancestry and explicit maintenance. Choose according to the balance between subtree reads and structural changes.
A string sort also differs from a numeric sibling order. Segment twelve can sort before segment four under lexical ordering. Store a separate sibling-order value or use a deliberate encoding when presentation order matters.
Protect the Invariant Over Time
Do your users move branches every hour or mostly read stable trees? That question changes the tradeoff more than the elegance of one SELECT. Test the workload that actually dominates the application.
Require a single reviewed path for insertions, moves, and deletions. Deleting a parent requires a defined child policy. Leaving descendants with orphaned ancestry is not a successful hierarchy cleanup.
I use a materialized path when explicit ancestry helps the dominant reads. I keep move and integrity checks beside the schema definition. Family trees deserve better than a string replacement performed with optimism.
Keep identifiers stable and prohibit wildcard characters in encoded segments. Review path-length growth when identifier ranges or maximum depth change. The format should remain a documented contract, not an accidental consequence of today's sample rows.
A hierarchy import should also detect duplicate identifier segments and missing intermediate routes. Check each child's stored route against its recorded parent plus the child identifier. A unique path index cannot detect those logical mismatches by itself.
Treat an integrity failure as a reason to stop mutation, not as permission to rewrite arbitrary prefixes. Recover from an authoritative parent relationship or approved source data. Verify descendant membership after rebuilding before reopening the hierarchy for writes.
Related reading on this blog: Making Recursive Parent-Child Queries Efficient and Quiz and Video: Introduction to Hierarchical Query using a Recursive CTE.
A stored route is not a self-maintaining tree, it is a useful read structure with explicit mutation rules.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
