Pfthb

If I don't have a join in the recursive part of my CTE, I only get one row per recursion, why is that?

Code tested on SQL Server 2016 and Azure SQL database:

DECLARE @Number TABLE (Number INT);

INSERT INTO @Number (Number)
VALUES (1), (2);

;WITH _cte AS 
(
 SELECT Number
 FROM @Number
 UNION ALL
 SELECT _cte.Number
 FROM _cte
)
SELECT *
FROM _cte
OPTION (MAXRECURSION 2); -- just call recursive part twice to see the issue

In the result I get number 2 in each recursion/depth.

I would expect the current rows to be repeated in each recursion, hence an exponential increase in the number of rows

Expected output

Number
-------------------- 
1 -- from anchor
2 
1 -- first recursion
2 
1 -- second recursion
2 
1 
2 

Actual output:

Number
-------------------- 
1 -- from anchor
2 
2 -- first recursion 
2 -- second recursion

From some testing, it appears that SQL recursion occurs depth first beginning with the last row returned from the initial query. As soon as it has reached MAXRECURSION, the query terminates with an error (e.g. The statement terminated. The maximum recursion 2 has been exhausted before statement completion.) and returns whatever results it reached before the error. You can verify this by inserting 2,1 instead of 1,2 in your insert statement, or by inserting 1,2,3 into your @Number table. If you insert 1,2,3 into @Number, your results returned would be

1 -- From anchor
2 -- From anchor
3 -- From anchor
3 -- 1st recursion from last row of anchor query
3 -- Recursion from the row from the line above this (2nd recursion)
-- Query terminates after MAXRECURSION reached on an item and does not attempt recursion on 1 or 2

Typically, when using recursion, you join the CTE to itself or some other table with some criteria in the where statement or the on criteria in the join that would limit the recursion based on the available data (not just repeated recursion through the same elements). For example (using the same @Number table from your question):

DECLARE @Number TABLE (Number INT);

INSERT @Number
(
 Number
)
VALUES (1),
(2);
WITH _cte2
AS
 (SELECT 
 Number
 ,0 as 'RecursionCount'
 FROM
 @Number

 UNION ALL
 SELECT
 Number
 ,RecursionCount + 1
 FROM
 _cte2
 WHERE
 RecursionCount <= 1
 )
SELECT * FROM _cte2 OPTION (MAXRECURSION 2);

In the above example, the query will limit its own recursion in the where statement and never reach MAXRECURSION, so the query will be able to finish. You'll also note that the order of results returned confirms my suspicion that recursion is depth first beginning with the last item:

Number RecursionCount
1 0
2 0
2 1
2 2
1 1
1 2

I changed your code a bit to this:

with 

 _cte as (

 SELECT num, 0 iteration
 FROM @Number

 UNION ALL
 SELECT num, iteration + 1
 FROM _cte
 where iteration <= 3 -- comment this line out and compare

 )

 SELECT *
 FROM _cte
 --OPTION (MAXRECURSION 2); -- comment this line out and compare as well

I then compared the results with that when commenting out the 'where iteration <= 3'. It took me awhile to realize, but the ordering of the results were not what I expected.

I think that when the error for max loops hits, or when you put in the option to cut off the max recursion, it is somehow cutting off the latter results that you would find in rows 7-10.