RECURSIVE CTE | SQL Server | SQL Tutorial

Recursive CTE:
A recursive CTE is a Table Expression that references itself. The Query repeatedly executes and returns subsets of data, until it returns the complete result set.​

It is defined by two Queries Anchor Member and Recursive Member.​

The Anchor member is a query that returns a relational result table, the anchor member query is invoked only once.
The recursive member in a query that has reference to the CTE name .

Both Anchor and Recursive Queries must be compatible in terms of columns

In Each subsequent invocation of the recursive member , the reference to the CTE name represents the result set returned by the previous invocation of the recursive member.
The recursive member is invoked repeatedly until it returns an empty result set or exceeds some limit.

The Outer Query for CTE represents the unified result set of Anchor member and Recursive member.

CTE in SQL Server