I'm not sure why Grant and others talk about update statistics, since everything is confined into a single script. There are no old statistics to update.

There is a new cardinality estimator, but I'm not sure that it matters for this case.

I ran the first script on SQL 2008 and after changing <= to >=, and it ran för two minutes and forty-five seconds. On SQL 2014, the script completed in 2:18, somewhat better.

I also tried to use Itzik Ben-Gans smart fn_nums:

CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS

RETURN

WITH

L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),

L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),

L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),

L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),

L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),

Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)

SELECT n AS Number FROM Nums WHERE n <= @n;

but I gave up when my query had not completed in four minutes.

I think it is better to have a table of numbers. While sometimes the optimizer can use these exploding CTEs correctly, it has little clue of what is going on and it may come up with complete crazy plans.