Translating Oracle CONNECT BY Queries to Recursive CTEs

An employee tree should survive a database move without changing its shape. Oracle CONNECT BY maps to a recursive CTE in SQL Server, but its ordering and cycle rules need explicit replacements.

Build a Small Employee Tree

Use a scratch session and an employee table with one manager per employee. The self-referencing relationship identifies each edge. A manager value of NULL marks a root. The sample permits a malformed cycle later so you can test the guard.

I start hierarchy translations with an actual drawing of the relationships. It catches a reversed join before the query becomes impressive. An upside-down organization chart is still an organization chart, just a very awkward one.

The source clause START WITH ManagerID IS NULL CONNECT BY PRIOR EmployeeID = ManagerID walks down from roots. In SQL Server, the anchor selects those roots. The recursive member joins each previous employee to the next level of direct reports. These are separate query parts joined with UNION ALL.

CREATE TABLE #Employee
(
    EmployeeID int NOT NULL PRIMARY KEY,
    ManagerID int NULL,
    EmployeeName nvarchar(50) NOT NULL
);
INSERT #Employee VALUES
(1, NULL, N'Avery'),
(2, 1, N'Blake'),
(3, 1, N'Casey'),
(4, 2, N'Drew'),
(5, 4, N'Ellis');
CREATE INDEX IX_Employee_Manager ON #Employee (ManagerID, EmployeeID);
SELECT EmployeeID, ManagerID, EmployeeName
FROM #Employee
ORDER BY EmployeeID;

Translate CONNECT BY PRIOR Into the Join Direction

In the source expression, PRIOR identifies the parent row's EmployeeID. The current row contributes ManagerID. The recursive join therefore reads e.ManagerID = t.EmployeeID. Reversing that join walks upward toward managers instead of downward toward reports.

Choose the anchor for the question. All roots produce a forest. A specific EmployeeID produces the subtree below that employee, even when that employee has a manager. An ancestor query instead starts with one employee and joins the previous ManagerID to the next EmployeeID.

Do you need every root, one department, or one employee's chain? Set that boundary before translating the rest. A WHERE filter outside the recursive query removes displayed rows afterward. A predicate inside the recursive member can prune a branch and all its descendants.

LEVEL Becomes a Carried Column

Oracle LEVEL starts at one for the root. Initialize Depth to one in the anchor and add one in the recursive member. Carry EmployeeID, ManagerID, and the name through the same structure. Matching column types across both members is mandatory.

This first query shows only the essential walk. It assumes the supplied acyclic sample. MAXRECURSION limits unexpected recursion, but does not diagnose the bad relationship. Its default is 100 recursive levels. Specify a deliberate bound rather than removing the protection during early testing.

;WITH EmployeeTree AS
(
    SELECT EmployeeID, ManagerID, EmployeeName, 1 AS Depth
    FROM #Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, t.Depth + 1
    FROM #Employee AS e
    JOIN EmployeeTree AS t ON e.ManagerID = t.EmployeeID
)
SELECT EmployeeID, ManagerID, EmployeeName, Depth
FROM EmployeeTree
ORDER BY Depth, EmployeeID
OPTION (MAXRECURSION 100);

SYS_CONNECT_BY_PATH Becomes an Accumulated String

A source expression such as SYS_CONNECT_BY_PATH(EmployeeName, '/') builds the names encountered from the root. Carry a path column and append the next name during recursion. Cast both members to the same sufficiently large string type.

A display path and a cycle-detection path serve different purposes. Names repeat and names contain punctuation. Use delimited employee identifiers for membership checks. The separators prevent employee 1 from falsely matching employee 11. Keep the display path readable, but keep the membership path unambiguous.

The later query uses nvarchar(max) for names and varchar(max) for identifiers. Those explicit casts prevent a recursive type mismatch. For production output, also define a maximum useful depth and path size. Unlimited storage types do not make unlimited traversal a sensible requirement.

Order Siblings With a Sort Path

The source ORDER SIBLINGS BY EmployeeName, EmployeeID sorts each parent's children while keeping each subtree together. Ordering the final result by EmployeeName sorts the entire tree globally. That loses the hierarchy's presentation order.

Assign each child a rank within its manager group first. Append fixed-width ranks to a carried sort path. The outer ORDER BY on that path produces a parent followed by its ordered descendants. EmployeeID breaks name ties. The following code assumes positive identifiers and the small supplied sample.

;WITH RankedEmployees AS
(
    SELECT EmployeeID, ManagerID, EmployeeName,
        ROW_NUMBER() OVER
        (PARTITION BY ManagerID ORDER BY EmployeeName, EmployeeID) AS SiblingRank
    FROM #Employee
), EmployeeTree AS
(
    SELECT EmployeeID, ManagerID, EmployeeName, 1 AS Depth,
        CAST(N'/' + EmployeeName AS nvarchar(max)) AS DisplayPath,
        CAST('/' + CONVERT(varchar(11), EmployeeID) + '/' AS varchar(max)) AS VisitedIDs,
        CAST(RIGHT('0000000000' + CONVERT(varchar(20), SiblingRank), 10) AS varchar(max)) AS SortPath
    FROM RankedEmployees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, t.Depth + 1,
        CAST(t.DisplayPath + N'/' + e.EmployeeName AS nvarchar(max)),
        CAST(t.VisitedIDs + CONVERT(varchar(11), e.EmployeeID) + '/' AS varchar(max)),
        CAST(t.SortPath + '/' + RIGHT('0000000000' + CONVERT(varchar(20), e.SiblingRank), 10) AS varchar(max))
    FROM RankedEmployees AS e
    JOIN EmployeeTree AS t ON e.ManagerID = t.EmployeeID
    WHERE CHARINDEX('/' + CONVERT(varchar(11), e.EmployeeID) + '/', t.VisitedIDs) = 0
)
SELECT EmployeeID, ManagerID, EmployeeName, Depth, DisplayPath
FROM EmployeeTree
ORDER BY SortPath
OPTION (MAXRECURSION 100);

Replace CONNECT BY NOCYCLE With a Cycle Guard

Oracle NOCYCLE allows traversal despite a loop. SQL Server needs an explicit check. The previous query rejects a candidate child already present in that branch's identifier path. This prevents endless recursion while retaining the valid portion of the branch.

That implements cycle avoidance, not every detail of Oracle's cycle pseudocolumns. If the application displays a cycle marker, define which row receives that marker. Do not claim identical output merely because both queries terminate. Multiple-parent relationships also require a separate edge-table design and path-specific decisions.

A cycle disconnected from every root never appears in a root-based walk. That is why cycle testing also needs a chosen starting employee. Reachability and valid topology are different checks. A report that looks complete can silently omit an entire malformed component.

Expose the Edge That Was Rejected

The final test creates a cycle in this temporary sample. Start from EmployeeID 1 rather than looking only for NULL managers. The walk keeps its identifier guard, then reports an outgoing edge pointing to an identifier already visited. Undo the sample change afterward.

I keep a diagnostic query beside the display query. Silent cycle avoidance protects execution, but it does not repair the organization data. The returned parent and repeated child identify the relationship to investigate. MAXRECURSION still limits a long valid chain as well as a broken query.

UPDATE #Employee SET ManagerID = 5 WHERE EmployeeID = 1;
;WITH Walk AS
(
    SELECT EmployeeID,
        CAST('/' + CONVERT(varchar(11), EmployeeID) + '/' AS varchar(max)) AS VisitedIDs
    FROM #Employee WHERE EmployeeID = 1
    UNION ALL
    SELECT e.EmployeeID,
        CAST(w.VisitedIDs + CONVERT(varchar(11), e.EmployeeID) + '/' AS varchar(max))
    FROM #Employee AS e
    JOIN Walk AS w ON e.ManagerID = w.EmployeeID
    WHERE CHARINDEX('/' + CONVERT(varchar(11), e.EmployeeID) + '/', w.VisitedIDs) = 0
)
SELECT w.EmployeeID AS ParentID, e.EmployeeID AS RepeatedChildID, w.VisitedIDs
FROM Walk AS w
JOIN #Employee AS e ON e.ManagerID = w.EmployeeID
WHERE CHARINDEX('/' + CONVERT(varchar(11), e.EmployeeID) + '/', w.VisitedIDs) > 0
OPTION (MAXRECURSION 100);
UPDATE #Employee SET ManagerID = NULL WHERE EmployeeID = 1;

Test the CONNECT BY Translation Before Trusting It

Test a root, a leaf, duplicate names, multiple roots, and a cycle. Compare depth numbering, path separators, sibling order, and branch filtering. Check missing managers separately, since a root-based query does not reveal every orphan.

Treat CONNECT BY as a collection of behaviors rather than one keyword replacement. Keep an index on the manager lookup for downward traversal. Inspect the actual plan and measured work on representative data before claiming performance equivalence.

When a limit is reached, the statement fails rather than delivering a certified complete hierarchy. Treat partial displayed rows as failed output. Raising the bound is appropriate only after checking the relationships and required depth. MAXRECURSION 0 removes the bound, so reserve it for deliberately validated designs.

Once those tests agree, the recursive query has a clear contract. Root choice, join direction, ordering, and termination are visible in the SQL. That makes the hierarchy maintainable after the migration ends.

Related reading on this blog: Oracle to SQL Server: Translating NVL, ROWNUM and Sequences and Making Recursive Parent-Child Queries Efficient.

A hierarchy translation is not a keyword swap, it is an explicit walk through relationships.

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