Prime Number Table Function

Creates a table of prime numbers, starting at 2 up to a maximum number.

Makes use of dbo.F_TABLE_NUMBER_RANGE, which is found here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

create function dbo.F_TABLE_PRIME(@MaxNumber bigint)
returns @t table (i bigint primary key) as
begin
insert @t select NUMBER from dbo.F_TABLE_NUMBER_RANGE(2, @MaxNumber)

declare @i bigint
set @i = 1
while 1 = 1
begin
select @i = min(i) from @t where i > @i
if @i is null or @i * @i > @MaxNumber break
delete @t where i > @i and i % @i = 0
end
return
end
go

select * from dbo.F_TABLE_PRIME(100000) order by iCan we improve on the speed?



Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.