Are there any downfalls in using recursive queries. Also, if a temp table created by a recursive query is larger than the allocated space, can it cause the database to crash?

With
Table1 As(Select ...)

Select ... from Table1;

1. What you quote in your message is not a recursive query.
2. Common table expression (the "WITH" part) does not necessarily create a temporary table.
3. If the database manager runs out of temporary space it shouldn't crash. However, the query itself will fail. Other queries may also fail if they use the temporary space as well.

Thanks a lot. I thought a common table expression always created a temp table for the duration of the select.

You can always look at the execution plan to see whether any particular query does it or not.