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:
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:
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.