Generating a sequence of integers as a table is a useful utility to have. Historically, this was done either by having a temporary table or falling back to a TSQL while loop. However, the UDT feature in SQL2000 and the CTE (common table expression) feature in SQL2005 make it easy to write a function that does this without explicitly looping or materializing the sequence.
Here is the code:
alter
function Ints(@n int)
returns table as
return
with Digits as
(
select 0 as num union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all select 15
),
Numbers as
(
select
d7.num * 16 * 16 * 16 * 16 * 16 * 16 * 16
+ d6.num * 16 * 16 * 16 * 16 * 16 * 16
+ d5.num * 16 * 16 * 16 * 16 * 16
+ d4.num * 16 * 16 * 16 * 16
+ d3.num * 16 * 16 * 16
+ d2.num * 16 * 16
+ d1.num * 16
+ d0.num as num
from Digits d0 cross join Digits d1 cross join Digits d2 cross join Digits d3 cross join
Digits d4 cross join Digits d5 cross join Digits d6 cross join Digits d7
where
d7.num <= @n / (4096 * 65536)
and d6.num <= @n / (256 * 65536)
and d5.num <= @n / (16 * 65536)
and d4.num <= @n / (65536)
and d3.num <= @n / (4096)
and d2.num <= @n / (256)
and d1.num <= @n / (16)
and d0.num <= @n
)
select * from Numbers where num < @n
For instance, if you really wanted to know what the harmonic series sum(1/n) from 1 to 1,000,000, then you could run the following query:
select sum(1.0 / (1.0 + num)) from Ints(1000000)
It comes back with the answer (14.39272671788580) in under two seconds on AMD 4200 X2. It automatically parallelized the query since two cores are available.
I am going to use this function in further notes on using SQL queries for unorthodox purposes.
