# Creating a Sequence Flush in SQL Server 2000

Monday Dec 8th 2003 by Eli Leiba
Share:

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.

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)
``````

Share:
Mobile Site | Full Site