How Does a Recursive CTE Execute Step-by-Step?

How Does a Recursive CTE Run, Line by Line?

Recursive CTEs, like endless UNION ALLs, consist of a base case and multiple recursive calls. Consider the following:

WITH    rows AS
        (
        SELECT  *
        FROM    mytable
        WHERE   anchor_condition
        ),
        rows2 AS
        (
        SELECT  *
        FROM    set_operation(mytable, rows)
        ),
        rows3 AS
        (
        SELECT  *
        FROM    set_operation(mytable, rows2)
        ),
        …
SELECT  *
FROM    rows
UNION ALL
SELECT  *
FROM    rows2
UNION ALL
SELECT  *
FROM    rows3
UNION ALL
…

In the provided example:

WITH    abcd1 AS
        ( 
        SELECT  *
        FROM    @tbl t
        WHERE   ParentId IS NULL 
        ),
        abcd2 AS
        ( 
        SELECT  t.*
        FROM    abcd1
        JOIN    @tbl t
        ON      t.ParentID = abcd1.id
        ),
        abcd3 AS
        ( 
        SELECT  t.*
        FROM    abcd2
        JOIN    @tbl t
        ON      t.ParentID = abcd2.id
        ),
        abcd4 AS
        ( 
        SELECT  t.*
        FROM    abcd3
        JOIN    @tbl t
        ON      t.ParentID = abcd3.id
        ),
        abcd5 AS
        ( 
        SELECT  t.*
        FROM    abcd4
        JOIN    @tbl t
        ON      t.ParentID = abcd4.id
        ),
        abcd6 AS
        ( 
        SELECT  t.*
        FROM    abcd5
        JOIN    @tbl t
        ON      t.ParentID = abcd5.id
        )
SELECT  *
FROM    abcd1
UNION ALL
SELECT  *
FROM    abcd2
UNION ALL
SELECT  *
FROM    abcd3
UNION ALL
SELECT  *
FROM    abcd4
UNION ALL
SELECT  *
FROM    abcd5
UNION ALL
SELECT  *
FROM    abcd6

Each anchor iteration yields a new recursive call, creating a set of nested layers. The anchor is called once, serving as the starting point. Subsequent iterations use the results of previous calls to populate the recursive member.

As abcd6 returns no results, it implies a stopping condition. Theoretically, recursive CTEs can be infinite, but SQL Server enforces a limit to prevent infinite recordsets.

Refer to the following article for further insights:

• [SQL Server: are the recursive CTE’s really set-based?](https://www.sqlshack.com/sql-server-recursive-ctes-really-set-based/)

The above is the detailed content of How Does a Recursive CTE Execute Step-by-Step?. For more information, please follow other related articles on the PHP Chinese website!