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

Eli Leiba
Eli works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 11 years experience working in the database field. Additionally, Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies.