Many organizations have complex hierarchies for their business processes. Building and retrieving data from these hierarchies in a relational database management system (RDBMS) can be a tedious task. However, SQL Server provides a better solution using Common Table Expressions (CTE).

Understanding Recursive CTE

A recursive CTE is a type of CTE that allows you to retrieve hierarchy-based data. It can be explained in three parts:

• Anchor Query: This is the first statement executed and provides the base data for the CTE.
• Separator: This is the middle part where you can use operators like UNION ALL to combine the results of the anchor query and the recursive query.
• Recursive Query: This is the main part of the CTE where recursion occurs. It refers to the same CTE and allows you to build the hierarchy.

Example: Creating an Organization Chart

Let’s take the example of an organization chart. We have a table called “MyDepartment” that represents the departments and their relationships. The hierarchy starts from the “CEO” and ends at the “Purchase Department”. Each department/person is linked to their predecessor as nodes.

Here’s how you can use a CTE to retrieve the entire organizational structure:

WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID, 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID IS NULL
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID, OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
SELECT * FROM OrgTree ORDER BY Tree

This query uses the CTE named “OrgTree” to retrieve the hierarchy. The anchor query selects the top-most node (CEO) with a NULL ParentID. The recursive query then joins the “MyDepartment” table with the CTE to build the hierarchy.

The result of this query will be a table that displays the DepartmentID, DepartmentName, ParentID, and the level of each department in the hierarchy.

Using MAXRECURSION to Avoid Infinite Loops

When using a recursive CTE, there is a possibility of entering an infinite loop if the parent and child queries return the same or equal values. To avoid this, you can use the MAXRECURSION option at the end of the SELECT command.

For example, you can set the MAXRECURSION value to 10 to limit the recursion depth:

WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID, 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID IS NULL
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID, OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
SELECT * FROM OrgTree OPTION (MAXRECURSION 10)

This query will stop the recursion after 10 levels, preventing an infinite loop.

Conclusion

Using Common Table Expressions (CTE) in SQL Server provides a more efficient and concise way to retrieve hierarchy-based data. By understanding the recursive CTE and using it effectively, you can easily navigate and query complex organizational structures.