Building Full Folder Paths From a Parent-Child Table

Full folder paths come from following the parent chain, not from reading one row. A recursive query builds them from the roots down, and the same query shows you which rows it could never reach.

Why a folder table needs a recursive query

A junior DBA once asked me why the screen showed “Sales” and nothing else. The table stored only the folder name and its parent number. The full path was never saved anywhere.

That is the normal design, and it is a good one. Each folder is stored once, with a ParentId pointing at another row. A root folder has no parent. To get the whole path you walk the chain, and a recursive common table expression does exactly that.

Let me build a tiny tree in a temp table. It has one healthy branch, plus two kinds of trouble I want you to meet on purpose: an orphan and a loop.

DROP TABLE IF EXISTS #Folders;

CREATE TABLE #Folders
(
    FolderId   int PRIMARY KEY,
    ParentId   int NULL,
    FolderName nvarchar(100) NOT NULL
);

INSERT #Folders (FolderId, ParentId, FolderName)
VALUES (1, NULL, N'Root'), (2, 1, N'Data'), (3, 1, N'Reports'), (4, 2, N'Sales'),
       (5, 99, N'Orphan'),
       (6, 7, N'LoopA'), (7, 6, N'LoopB');

Build the path from the root down

The first half of the query, the anchor, picks the rows with no parent. The second half joins each child to the parent row it just found, and adds the child name to the path. Depth counts how many steps down we are.

I cast the path to nvarchar(max) on both sides. The two halves must return the same types, and a long tree should not run out of room. The last line sorts by path, with FolderId to break ties.

WITH Paths AS
(
    SELECT FolderId, CAST(FolderName AS nvarchar(max)) AS FullPath, 0 AS Depth
    FROM #Folders
    WHERE ParentId IS NULL

    UNION ALL

    SELECT c.FolderId, CAST(p.FullPath + N'\' + c.FolderName AS nvarchar(max)), p.Depth + 1
    FROM #Folders AS c
    JOIN Paths AS p ON c.ParentId = p.FolderId
)
SELECT FolderId, FullPath, Depth
INTO #Paths
FROM Paths
OPTION (MAXRECURSION 100);

SELECT FolderId, FullPath, Depth
FROM #Paths
ORDER BY FullPath, FolderId;

You get four rows: Root, Root\Data, Root\Data\Sales and Root\Reports, with depths 0, 1, 2 and 1. Look at what is missing. Orphan, LoopA and LoopB never show up. No error, no warning, they simply are not in the answer.

That is the trap. A report built on this query looks complete, and the damaged rows quietly disappear.

Find the rows the walk never reached

Because I saved the paths in #Paths, finding the missing rows is a simple anti-join. Anything in the table that has no path was not reachable from a root.

SELECT f.FolderId, f.ParentId, f.FolderName
FROM #Folders AS f
WHERE NOT EXISTS (SELECT 1 FROM #Paths AS p WHERE p.FolderId = f.FolderId)
ORDER BY f.FolderId;

Three rows come back. Orphan points at parent 99, which does not exist. LoopA points at LoopB and LoopB points back at LoopA, so neither has a route from a root.

Run this check on your real table before you trust any path report. An empty result is what you want.

Why walking down is safe and walking up is not

Each row has only one parent. So a loop can never be reached from a root: every row in the loop has its parent inside the loop. That is why LoopA and LoopB stayed out of the result above.

Walk the other way, from a leaf up to its root, and the loop becomes a real danger. Start at LoopA and keep asking for the parent. It never ends, so I set a small MAXRECURSION limit and let it fail.

WITH Up AS
(
    SELECT FolderId, ParentId, FolderName, 0 AS Steps
    FROM #Folders
    WHERE FolderId = 6

    UNION ALL

    SELECT f.FolderId, f.ParentId, f.FolderName, u.Steps + 1
    FROM #Folders AS f
    JOIN Up AS u ON f.FolderId = u.ParentId
)
SELECT FolderId, FolderName, Steps
FROM Up
OPTION (MAXRECURSION 5);

The output shows LoopA, LoopB, LoopA, LoopB, over and over. Then SQL Server stops with error 530, saying the maximum recursion of 5 was exhausted. Keep a limit like that in any upward walk. It is a seatbelt, not a fix. The fix is to find and repair the bad row.

Last, a design note. I compute paths when someone asks instead of storing them. If you store the path in every row, a rename of one parent folder means rewriting every descendant.

DROP TABLE IF EXISTS #Paths;
DROP TABLE IF EXISTS #Folders;

Next time a path report looks tidy, ask which rows it left out.

A full path is not a column you read, it is a chain you walk.

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