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)
