Prime Numbers
Posted by Philip Leitch
Friday, August 07, 2009 12:41:27 AM
Recently there was a competition on creating prime nubmers in SQL Server. Although there were faster versions, I believe that this is the fastest version that will scale.
create table Primes (p integer NOT NULL Primary Key)
go
set nocount on
-- Seed the pot with the first two Primes
declare @testval integer, @First as int, @Second as int, @SQRT as int
-- Start testing at n=6, stepping 6 per test.
set @testval = 6
BEGIN TRANSACTION
while @testval < 5000000
begin
set @First = @testval -1
set @Second = @testval +1
set @SQRT = SQRT(@testval +1)
if not exists (select @First from Primes where (@First) % p = 0 and p <= @SQRT)
insert Primes select @First
if not exists (select @Second from Primes where (@Second) % p = 0 and p <= @SQRT)
insert Primes select @Second
set @testval = @testval + 6
end
INSERT Primes select 2
INSERT Primes select 3
COMMIT
Copyright 2009 Philip Leitch