Using SQL Server CTE recursive query to process trees, graphs, and hierarchies _mssql

The CTE (Common Table Expressions) is a version of SQL Server 2005. The specified temporary named result set, which is called a CTE. Similar to derived tables, is not stored as an object and is valid only for the duration of the query. Unlike a derived table, a CTE can be referenced and can be referenced multiple times in the same query. Using a CTE improves code readability without compromising its performance.

A recursive CTE is one of the most important enhancements in SQL SERVER 2005. In general, we need recursive queries when dealing with tree, graph and hierarchy problems.

The syntax of a CTE is as follows

 
 with the CTE as (
   SELECT EmpId, Reportto, FName from employ WHERE empid=
   UNION all
   SELECT emp. EmpId, EMP. Reportto, EMP. FName from CTE JOIN employ as EMP on CTE. Empid=emp. Reportto
 )

A recursive CTE contains at least two queries (also known as members). The first query is a fixed-point member, and the fixed-point member is simply a query that returns a valid table for a recursive basis or anchor point. The second query, called a recursive member, makes the query known as a recursive member as a recursive reference to a CTE name that is triggered. The internal application of the CTE name can logically be interpreted as the result set of the previous query.

Recursive queries do not have an explicit recursive termination condition, which stops recursion only if the second recursive query returns an empty result set or exceeds the maximum number of recursive times. The method that refers to the upper limit of recursive number is to use maxrecurion.

 Use AdventureWorks;
 Go
 --creates a infinite loop with
 CTE (EmployeeID, ManagerID, Title) as
 (
   SELECT EmployeeID, ManagerID, Title from
   humanresources.employee
   WHERE ManagerID are not NULL
  UNION all
   SELECT CTE. EmployeeID, CTE. ManagerID, CTE. Title from
   CTE 
   JOIN HumanResources.Employee as E on 
     CTE. ManagerID = E.employeeid
 )
 --uses maxrecursion to limit the recursive levels 
 to SELECT EmployeeID, Manager ID, Title from
 CTE
 OPTION (maxrecursion);
 Go

The above content is introduced in this article to the use of SQL Server CTE recursive query processing trees, graphs and hierarchies, I hope you like.