Creating a Sequence Flush in SQL Server 2000

This user-defined in-line T-SQL function for SQL Server 2000 creates an in-memory sequence flush 1,2,3....N where N is a natural number with a value less than 1000. If N needs to be greater than 1000, the function can be modified to satisfy this as well.

Explanation:

By using a cross join between three derived tables that consist of numbers 0,1,2,3,4,5,6,7,8, and 9, we get all the permutations from Point (0,0,0) to Point (9,9,9). There are exactly 10^3 such permutations (recall from combinatorics).

By doing f(x,y,z) = 100 x + 10y + z, we get all the numbers from 0 to 999 and then we add 1 to complete the process.

Parameter @n serves as the upper bound of the sequence flush.

Here is the code:

Create Function dbo.fn_sequence (@n smallint)
RETURNS TABLE 
AS
RETURN 
SELECT top 100 percent 100 * a.x + 10 * b.y + c.z + 1 as V
from 
(select 0 as x  union all 
        select 1 as x  union all
        select 2 as x  union all
        select 3 as x  union all
        select 4 as x  union all
        select 5 as x  union all 
        select 6 as x  union all
        select 7 as x  union all
        select 8 as x  union all
        select 9 as x  
         )  a
,
       (select 0 as y  union all 
        select 1 as y  union all
        select 2 as y  union all
        select 3 as y  union all
        select 4 as y  union all
        select 5 as y  union all 
        select 6 as y  union all
        select 7 as y  union all
        select 8 as y  union all
        select 9 as y  
         )  b 
,
       (select 0 as z  union all 
        select 1 as z  union all
        select 2 as z  union all
        select 3 as z  union all
        select 4 as z  union all
        select 5 as z  union all 
        select 6 as z  union all
        select 7 as z  union all
        select 8 as z  union all
        select 9 as z  
         )  c
where 100 * a.x + 10 * b.y + c.z < @n
order by 1 asc
go

function Usage 

--Example  Getting numbers 1..117
select v from dbo.fn_sequence (117)