Prime number generator using a recursive SQL cte

Question

The following is an attempt at a prime number generator using a recursive CTE:

WITH RECURSIVE prime(num, is_prime) AS (
    SELECT 1, 1
    UNION ALL
    SELECT 
        num+1, 
        IF( num+1 IN (2,3,5,7) OR (
            (num+1)%2!=0 AND 
            (num+1)%3!=0 AND 
            (num+1)%4!=0 AND 
            (num+1)%5!=0 AND 
            (num+1)%6!=0 AND 
            (num+1)%7!=0 AND 
            (num+1)%8!=0 AND 
            (num+1)%9!=0 AND 
            (num+1)%10!=0), 
          num+1, NULL
         )
    FROM prime WHERE num < 100
) SELECT * FROM prime;

┌─────┬──────────┐
│ num ┆ is_prime │
╞═════╪══════════╡
│   1 ┆        1 │
│   2 ┆        2 │
│   3 ┆        3 │
│   4 ┆          │
│   5 ┆        5 │
│   6 ┆          │
│   7 ┆        7 │
│   8 ┆          │
 ...etc...

I know that this includes items that can be optimized out (such as MOD 9 or MOD 10), but I'm leaving them in there to make things more explicit. Is there another way to do this without having to write out each case individually? For example, having something like RANGE(10) and doing the iteration based on that (without having to write procedural code/extensions).

What might be a creative way to do this, maybe even using another recursive CTE?

Answer 1

Not sure how high you need to go, but I tested this up to a ceiling of 10,000 which returned in 1229 prime number results.

WITH Numbers AS (
    SELECT   2 AS Number
    UNION ALL
    SELECT   Number + 1
    FROM     Numbers
    WHERE    Number < 10000) -- Highest prime number to include

,Primes AS (
    SELECT   Number
    FROM     Numbers
    WHERE NOT EXISTS (
        SELECT   1
        FROM     Numbers n
        WHERE    n.Number < Numbers.Number
        AND      Numbers.Number % n.Number = 0))

SELECT   TOP 2000 Number -- Max number of results to return
FROM     Primes
OPTION   (MAXRECURSION 0)