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.