Weird, your CTE code took 16 seconds (twice) on my SQL Server

that is not VERY VERY fast. Anyway, we don't have the need for it, I was just curious

16 seconds on a SQL Server on VMWare, and 16 seconds on a physical SQL Server (8 cpu, 24GB RAM)

WITH

L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows

L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows

L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows

L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows

L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows

L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows

num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)

SELECT N FROM NUM WHERE N <= 1000000;