Alternative manner to implement an hierarchy without using recursive CTEs

Question

Alternative manner to implement an hierarchy without using recursive CTEs

pmscorca 1,052

Hi,

I'm searching an alternative manner to implement an hierarchy in T-SQL without using recursive CTEs. Actually, a MS Fabric warehouse doesn't support a such useful feature and I need to use recursive CTEs in some scenarios.

Any suggests to me, please? Many thanks

pmscorca 1,052 Reputation points

2025-12-16T07:42:31.1733333+00:00

The hierarchy exists, I need to navigate it as using a recursive CTE. Thanks
Viorel 125.7K Reputation points

2025-12-16T08:00:22.6933333+00:00

For example, if each row contains a reference (foreign key) to parent node, what kind of navigation is needed?
pmscorca 1,052 Reputation points

2025-12-16T11:09:01.1233333+00:00

Hi, I need to implement a recursive CTE by an UNION ALL between two SELECTs.

I need to simulate the behaviour of the Qlik function named HierarchyBelongsTo that simply checks the membership of a node in a hierarchy with respect to a root node.
Zeeshan Zaidi 0 Reputation points

2025-12-16T11:31:56.3733333+00:00

If recursive CTEs aren’t supported, you still have a few good options in T-SQL. One common approach is using a parent-child table with a path or level column (like materialized path). Another option is adjacency list + WHILE loop to iterate through levels and build the hierarchy step by step.

If the hierarchy is mostly read-heavy, you can also consider closure tables or nested sets, which avoid recursion at query time and work well in environments with limited SQL features.
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2025-12-16T22:33:04.7633333+00:00

Zeeshan makes some good points. I found this post, which seems to describe nested sets well: https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets

Nested sets are a lot more efficient to traverse hierachies than the normal tree structures. Changes to the structure can be a lot more painful. And in any case, it's not very intuitive and user-friendly. I've never used it myself. But then again, I have not been in Fabric Datawarehouse.

To get answers that are more precise for Fabric DW, you may want to visit the forums at https://community.fabric.microsoft.com/.
Viorel 125.7K Reputation points

2025-12-17T02:44:13.8366667+00:00

If the hierarchy level is reasonably limited, some recursive statements can be replaced with single statements that contain a series of JOINs.

1 answer

Your answer

pmscorca 1,052 Reputation points

2025-12-16T07:42:31.1733333+00:00

The hierarchy exists, I need to navigate it as using a recursive CTE. Thanks
Viorel 125.7K Reputation points

2025-12-16T08:00:22.6933333+00:00

For example, if each row contains a reference (foreign key) to parent node, what kind of navigation is needed?
pmscorca 1,052 Reputation points

2025-12-16T11:09:01.1233333+00:00

Hi, I need to implement a recursive CTE by an UNION ALL between two SELECTs.

I need to simulate the behaviour of the Qlik function named HierarchyBelongsTo that simply checks the membership of a node in a hierarchy with respect to a root node.
Zeeshan Zaidi 0 Reputation points

2025-12-16T11:31:56.3733333+00:00

If recursive CTEs aren’t supported, you still have a few good options in T-SQL. One common approach is using a parent-child table with a path or level column (like materialized path). Another option is adjacency list + WHILE loop to iterate through levels and build the hierarchy step by step.

If the hierarchy is mostly read-heavy, you can also consider closure tables or nested sets, which avoid recursion at query time and work well in environments with limited SQL features.
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2025-12-16T22:33:04.7633333+00:00

Zeeshan makes some good points. I found this post, which seems to describe nested sets well: https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets

Nested sets are a lot more efficient to traverse hierachies than the normal tree structures. Changes to the structure can be a lot more painful. And in any case, it's not very intuitive and user-friendly. I've never used it myself. But then again, I have not been in Fabric Datawarehouse.

To get answers that are more precise for Fabric DW, you may want to visit the forums at https://community.fabric.microsoft.com/.
Viorel 125.7K Reputation points

2025-12-17T02:44:13.8366667+00:00

If the hierarchy level is reasonably limited, some recursive statements can be replaced with single statements that contain a series of JOINs.

Answer 1

One alternative manner to implement a hierarchy in T-SQL without using recursive CTEs is to utilize the parent/child approach. In this method, each row in your table contains a reference to its parent, allowing you to establish a hierarchical relationship without recursion. Here’s an example of how you can set this up:

CREATE TABLE ParentChildOrg (
    BusinessEntityID INT PRIMARY KEY,
    ManagerId INT FOREIGN KEY REFERENCES ParentChildOrg(BusinessEntityID),
    EmployeeName NVARCHAR(50)
);
GO

In this example, the ManagerId column references the BusinessEntityID of the parent, creating a direct link between each employee and their manager. This structure allows for efficient querying of the hierarchy without the need for recursive CTEs, especially when the hierarchy does not change frequently.

Additionally, consider using XML data types if your application requires storing and retrieving the complete hierarchy in XML format. This can be beneficial when the data is consumed in XML format and predicate searches are limited.

References:

When to use alternatives to hierarchyid

Share via

Alternative manner to implement an hierarchy without using recursive CTEs

1 answer

Your answer