Home >Backend Development >PHP Tutorial >Implementing prime number calculation in SQLSERVER2005_PHP tutorial
I will raise a challenge, who can come up with the best way to calculate prime numbers using SQLSEERVER,
I used a new feature CTE and some TSQL implementation, but neither is ideal, the former (CTE) has limitations, and the latter ( TSQL) It took 7 minutes to generate one million prime numbers
Can you do better?
Here are some code snippets of mine
(TSQL implementation)
set nocount on
declare @prime table (prime int not null primary key)
--insert into @prime values (2 )
--insert into @prime values (3)
--insert into @prime values (5)
--insert into @prime values (7)
--insert into @prime values (11)
declare @number int, @pc int
set @number = 13
set @pc = 1
while @pc < 1000000
begin
if not exists ( select 1 from @prime where @number % prime = 0 and prime < sqrt(@number) )
begin
insert into @prime select @number
set @pc = @pc 1
end
set @number = @number
case when @number %2 = 1 then 2
when @number %3 = 2 then 2
when @number %5 = 4 then 2
when @number %7 = 6 then 2
when @number = 10 then 2
else 1 end
end
select @pc
and
(CTE implementation)
with seq
as( select 13 number
union all
select s.number
case when s.number %2 = 1 then 2
when s.number %3 = 2 then 2
when s.number %5 = 4 then 2
when s.number %7 = 6 then 2
when s.number = 10 then 2
else 1 end
from seq s
where number < 32767
)
, prime as (
select s.number
from seq s
where not exists ( select 1 from seq s2 where s2.number < s. number and (s.number) % s2.number = 0)
)
select *
from prime
option (MAXRECURSION 32767)