Option 1: using numbers table

SELECT TOP (1000000) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))

INTO dbo.Numbers –-[optional] if you want into data into a table

FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2

OPTION (MAXDOP 1);

SELECT TOP (1000) n FROM dbo.Numbers ORDER BY n;

Option 2: using spt_values

SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY number)

FROM [master]..spt_values ORDER BY n;

Option 3: using sys.all_objects

SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n;

Option 4: using Stacked CTEs

;WITH e1(n) AS

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), -- 10

e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10

e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100

SELECT n = ROW_NUMBER() OVER (ORDER BY n) FROM e3 ORDER BY n;

Option 5: using Recursive CTE

;WITH n(n) AS

(

SELECT 1

UNION ALL

SELECT n+1 FROM n WHERE n < 1000

)

SELECT n FROM n ORDER BY n

OPTION (MAXRECURSION 1000);

Put a where condition with value%2=0 to get even numbers

Put a where condition with value%2=1 to get even numbers