Introduction to SQL Server recursive CTE
 in this sample you will learn how to use the SQL Server recursive CTE to query hierarchical data.

A recursive common table expression (CTE) is a CTE that references itself. By doing so, the CTE repeatedly executes, returns subsets of data, until it returns the complete result set.

A recursive CTE has three parts:

1. An initial query that returns the base result set of the CTE. The initial query is called an anchor member.
2. A recursive query that references the common table expression, therefore, it is called the recursive member.
    The recursive member is union-ed with the anchor member using the UNION ALL operator.
3. A termination condition specified in the recursive member that terminates the execution of the recursive member.

The execution order of a recursive CTE is as follows:
First, execute the anchor member to form the base result set (R0), use this result for the next iteration.
Second, execute the recursive member with the input result set from the previous iteration (Ri-1) and return a sub-result set (Ri) until the termination condition is met.
Third, combine all result sets R0, R1, … Rn using UNION ALL operator to produce the final result set. 
 
IMPORTANT: 
Great care must be taken when using CTEs to avoid falling into the scenario of creating an infinite loop.
To solve situations that can generate infinite loops, it is always recommended to use the MAXRECURSION command together with the CTEs.
MAXRECURSION can be used to prevent a malformed recursive CTE from entering an infinite loop, limiting the number of lines produced by its final result.
In summary, when using MAXRECURSION 10, for example, you tell SQL to finalize and return the result with a maximum of 10 lines.