How does a Recursive CTE run, line by line?

by

Think of a recursive CTE as of an endless UNION ALL:

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 your case, that would be:

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

Since abcd6 yields no results, this implies a stopping condition.

Theoretically, a recursive CTE can be infinite, but practically, SQL Server tries to forbid the queries that would lead to infinite recordsets.

You may want to read this article:

More Related Contents:

  1. Is it possible to make a recursive SQL query?
  2. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  3. how to check child table all column values are negative in sql
  4. SQL Server – Best way to get identity of inserted row?
  5. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  6. PostgreSQL “Column does not exist” but it actually does
  7. How to pass parameters to query?
  8. Is the NOLOCK (Sql Server hint) bad practice?
  9. SQL: find missing IDs in a table
  10. Pass table as parameter into sql server UDF
  11. There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key
  12. MySQL: Transactions vs Locking Tables
  13. SQL UPDATE order of evaluation
  14. How to show all privileges from a user in oracle?
  15. Finding the next available id in MySQL
  16. Error message: (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)
  17. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  18. SQL – Query to get server’s IP address
  19. Merge two rows in SQL
  20. PostgreSQL Where count condition
  21. How to write a constraint concerning a max number of rows in postgresql?
  22. SQL Transpose Rows as Columns
  23. How to return two columns with function
  24. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  25. PostgreSQL how to create a copy of a database or schema?
  26. Is there a severe performance hit for using Foreign Keys in SQL Server?
  27. PostgreSQL: fill NULL values in timeserie query with previous value
  28. Date serial in SQL?
  29. Hamming Distance / Similarity searches in a database
  30. Get most common value for each value of another column in SQL

Leave a Comment