What to consider when generating rows via cross joins vs recursive unions?
Community wiki answer- please edit to improve:

Depending on how many rows you're generating, the second query may be slower as the recursive part of your CTE will be forced to run serially.

The recursive CTE solution may also have poorer cardinality estimates, because SQL Server finds it difficult to assess how many rows will be produced.

The query optimizer recognizes that the results of the first query are already sorted but does not recognize that for the recursive query. This means that using the recursive approach in a query that benefits from sorted data may lead to an unnecessary sort.

The recursive CTE as is can only generate up to 101 rows before hitting an error. To get a larger result set you need to add a MAXRECURSION query hint.

As an aside, regardless of which method you prefer, if you terminate every statement with a semicolon, you don't need to put another semicolon at the beginning, i.e. it's all right to have with … instead of ;with ….

Related:


SQL, Auxiliary table of numbers
What is the best way to create and populate a numbers table? on Stack Overflow