Finding prime numbers with SQL server

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

Today I just wanted to have a little fun, while at the same time brushing off my T-SQL skills. As you know, prime numbershave many uses, for example in encryption. And while they are easy to find in the beginning, it gets increasingly harder as the numbers go up. Below is the little snippet of code that prints out prime numbers.

It has 2 parameters that you can use to control it, @a is the first number to evaluate and @b is the last.

The rest is basically 2 nested loops, one that counts from @a to @b, and one that evaluates @a to see if it is a prime number by dividing it by all numbers between 2 and @a/2. The little trick with @a/2 makes it twice as fast as without 🙂 

UPDATE: It turns out, that in fact much time can be saved by just dividing with all integers between 2 and sqrt(@2) so the code below is updated, and it now runs in 2 seconds 🙂

Enjoy!

declare @b int
declare @c float
declare @d int

select @a = 10
select @b = 10000
select @c = @a
select @d = 0


while @a < @b 
begin

select @c = (convert(int, sqrt(@a)) ) + 1

while @c > 1
begin


if (select (@a/@c) - (convert(int, @a/@c) )) = 0
  select @d=@d+1
  if (select (@a/@c) - (convert(int, @a/@c) )) = 0
  break
select @c=@c-1
end
if (select @d) = 0
  print convert(varchar(10),@a) + ' is a prime number'
select @d = 0
select @a=@a+1
end

It is not the fastest prime number finder in the world, but I clocked it at 50 2 seconds to find all primes between 10 and 10000 and 1 minute and 17 seconds to find all primes between 10 and 100.000. Can you beat that with T-SQL? in that case, post the code in a comment !

Läs liknande inlägg