Use SqlServer CTE to recursively query the processing tree, graph, and hierarchy, and sqlservercte

Use SqlServer CTE to recursively query the processing tree, graph, and hierarchy, and sqlservercte

CTE (Common Table Expressions) is available in Versions later than SQL Server 2005. The specified temporary naming result set, which is called CTE. Similar to a derived table, it is not stored as an object and is only valid during query. Unlike the derived table, the CTE can be referenced by itself or multiple times in the same query. Using CTE can improve code readability without compromising its performance.

Recursive CTE is one of the important enhancements in SQL SERVER 2005. Recursive queries are generally used when dealing with tree, graph, and hierarchy problems.

The syntax of CTE is as follows:

 WITH 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 )

Recursive CTE contains at least two queries (also called members ). The first query is a fixed-point member. A fixed-point member is only a query that returns a valid table and is used as the basis or positioning point for recursion. The second query is called a recursive member, and the recursive reference to the CTE name is triggered. Logically, the internal application of the CTE name can be understood as the result set of the previous query.

Recursive queries do not have explicit recursive termination conditions. Recursive queries are stopped only when the second recursive query returns an empty result set or exceeds the maximum number of recursion times. MAXRECURION is used to limit the number of recursion times.

 USE AdventureWorks; GO --Creates an infinite loop WITH cte (EmployeeID, ManagerID, Title) as (   SELECT EmployeeID, ManagerID, Title   FROM HumanResources.Employee   WHERE ManagerID IS 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, ManagerID, Title FROM cte OPTION (MAXRECURSION ); GO

The above content is the processing tree, graph, and hierarchy of SQL Server CTE recursive query.