SQL Prime number function

Question

If I have a number X and want to say IsPrime(X) = true/false using sql-server what is the best approach?

Do I just import a table of primes or is there an algorithm that is fairly efficient for the smaller primes?

Note: I'm not interested in numbers greater than approx. 10 million.

Ended up using the following:

CREATE FUNCTION [dbo].[isPrime]
(
    @number INT
)
RETURNS VARCHAR(10)

BEGIN


    DECLARE @retVal VARCHAR(10) = 'TRUE';

    DECLARE @x INT = 1;
    DECLARE @y INT = 0;

    WHILE (@x <= @number )
    BEGIN

            IF (( @number % @x) = 0 )
            BEGIN
                SET @y = @y + 1;
            END

            IF (@y > 2 )
            BEGIN
                SET @retVal = 'FALSE'
                BREAK
            END

            SET @x = @x + 1

    END

    RETURN @retVal
END

Answer 1

Here's a combination of number generation and checking for prime numbers.

This is a quick running prime number checker.

The generator will create a list of numbers up to 10,000.
The @CheckNumber can be set to any value that is needed.

Feel free to scale up the max generated number to meet your needs.

DECLARE @CheckNumber INT;
SET @CheckNumber = 29;

with data as (
    SELECT (ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n ) + 1 as number
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
)

select sub1.number1
from (
  select distinct d1.number as number1, d2.number as number2
  from data as d1 
  cross join data as d2
  where d1.number % d2.number != 0
) as sub1
group by sub1.number1
having count(sub1.number2) = max(sub1.number2)-2
and sub1.number1 = @CheckNumber
order by sub1.number1