Finding primes is something I worked on about 6 months ago (for fun, of course ). Here's a link to the work...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69646

It uses a sieve of Atkin approach, and in my tests back then, the function could find all primes below 100,000 in 0.5 seconds.

By checking the square root outside of the select statement, you will save the processor a number of extra calculations.  I modified it to be this:

 SELECT @NextIntSqrt = SQRT(@NextInt)
 IF NOT EXISTS (SELECT Prime FROM Primes WHERE @NextIntSqrt >= Prime AND @NextInt % Prime = 0)

Cuts down the calculation time significantly.  I also adjusted the order of the filters in the WHERE clause.

Kathi

In the book you mentioned, Chris has Aspbergers Syndrome (rather than being autistic). There are many similarities, but people with Aspbergers for the most part can lead relatively 'normal' lives. There are varying degrees of both of course, and it's sometimes hard to make a clear distinction where one ends and the other begins.

Regards,

MItch

Hi Ryan,

What did you come up with for the largest prime number found and how many prime numbers?   I wanna make sure I'm doing it right...

My suggestion, on our SQL Server 2000 installation it finds all primes less than 5000000 in 54 seconds. But of course it depends on the hardware...

set nocount on
go

select top 5000000 identity(int, 1, 1) as Number into #Numbers
from syscomments c1 cross join syscomments c2
go

alter table #Numbers add constraint PK_Numbers primary key clustered (Number)
go

create table #Primes(prime int primary key)
go

declare @time datetime
select @time = getdate()

declare @i int
set @i = 1
while @i*@i < 5000000
begin
 insert into #Primes
 select n.Number
 from #Numbers n
 where
 n.Number < (@i+1)*(@i+1) and n.Number > @i*@i
 and not exists
 (
  select * from #Primes p
  where p.prime < @i + 1 and n.Number % p.prime = 0
 

 set @i = @i + 1
end

--select * from #Primes

select datediff(ms, @time, getdate())

drop table #Primes
go

drop table #Numbers
go