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=69646It 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.
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.
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 ongo
select top 5000000 identity(int, 1, 1) as Number into #Numbersfrom syscomments c1 cross join syscomments c2go
alter table #Numbers add constraint PK_Numbers primary key clustered (Number)go
create table #Primes(prime int primary key)go
declare @time datetimeselect @time = getdate()
declare @i intset @i = 1while @i*@i < 5000000begin 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 + 1end
--select * from #Primes
select datediff(ms, @time, getdate())
drop table #Primesgo
drop table #Numbersgo