What to consider when generating rows via cross joins vs recursive unions? These two queries return the same result (generating a sequence of integers):

1
2
3
4
5
...
16


What performance/other considerations should be taken into account when choosing one or the other?

A (from a blog post)

;with
T1(number) AS ( SELECT 1 UNION ALL SELECT 1 ),
T2(number) AS ( SELECT 1 FROM T1 a cross join T1 b ),
T3(number) AS ( SELECT 1 FROM T2 a cross join T2 b ),
Nums(number) AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) from T3 )
select * from Nums;


B

;with Ints(i) as (
    select 1
    union all
    select (i + 1)
    from Ints
    where i &lt; 16
)
select * from Ints;


I prefer B...
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