BFS Traversal In MSSQL
You will get better performance using a recursive common table expression although the syntax is (some would say) a bit more complicated, fortunately I don't have to explain it as there is an excellent article by Itzik where he handles both breadth first and depth first hierarchy evaluation.
https://sqlperformance.com/2020/09/t-sql-queries/fundamentals-of-table-expressions-part-6-recursive-ctes