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)


»


See All Articles by Columnist
Eli Leiba

Eli Leiba
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.

Latest Articles