Categories
Numbers Script SQL

SQL Generate Tally (Number) Table

-- select top 10 [n] from [dbo].[GetNumbers]( 1024 ) order by [n];
create or alter function [dbo].[GetNumbers]( @n bigint )
returns table with schemabinding as
return
-- declare @n bigint = 1024;
with
	l0 as (select 0 [c] union all select 1),
	l1 as (select 1 [c] from l0 [a] cross join l0 [b]),
	l2 as (select 1 [c] from l1 [a] cross join l1 [b]),
	l3 as (select 1 [c] from l2 [a] cross join l2 [b]),
	l4 as (select 1 [c] from l3 [a] cross join l3 [b]),
	l5 as (select 1 [c] from l4 [a] cross join l4 [b]),
	nums as(select row_number() over(order by (select null)) as n from l5)
  select top (@n) n
  from nums
  order by n;