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;

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s