Post History

For small tables you can bound the recursion depth:

    WITH my_cte(childId, parentId, depth, max_depth)
    AS (
        SELECT r.childId, r.parentId, 1, (SELECT COUNT(*) FROM My_Table)
        FROM My_Table r
        WHERE r.childId = 1

    UNION ALL

        SELECT rel.childId, rel.parentId, sd.depth + 1, sd.max_depth
        FROM My_Table rel INNER JOIN my_cte sd ON rel.childId = sd.parentId
        WHERE sd.depth < sd.max_depth
    )
    SELECT DISTINCT childId, parentId
    FROM my_cte

This approach is limited by the bound that [`MAXRECURSION <= 32767`](https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15).