MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

 » Database Journal Home » Database Articles » Database Tutorials MS SQL Oracle DB2 MS Access MySQL » RESOURCES Database Tools SQL Scripts & Samples Tips » Database Forum » Slideshows » Sitemap

## SQL Scripts & Samples

Posted Dec 8, 2003

# Creating a Sequence Flush in SQL Server 2000

By Eli Leiba

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

SQL Scripts & Samples Archives

Comment and Contribute

(Maximum characters: 1200). You have characters left.

 Latest Forum Threads SQL Scripts & Samples Forum Topic By Replies Updated sql help!! add 1 August 22nd, 11:58 AM How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM solving query svibuk 1 February 3rd, 06:08 AM converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM