Following on from my previous SQL script to find prime numbers

https://devrant.com/rants/2218452/...

I wondered whether there was a way to improve it by only checking for prime factors. It feels really dirty to use a WHILE loop in SQL, but I couldn't think of another way to incrementally use the already found prime numbers when checking for prime factors.

It's fast though, 2 mins 15 seconds for primes under 1,000,000 - previous query took over an hour and a half.

The SQL:

CREATE TABLE #PrimeNumbers (Number INT NOT NULL);

-- Insert 2 as first prime number
INSERT #PrimeNumbers (Number)
VALUES
(2);

-- Add a primary key
ALTER TABLE #PrimeNumbers
ADD CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED (Number);

DECLARE @Tested INT = 3;
DECLARE @MaxTested INT = 1000000;

WHILE @Tested < @MaxTested
BEGIN
INSERT #PrimeNumbers (Number)
SELECT @Tested
-- @Tested is a prime number if it has no prime factors with value less than half of itself
WHERE NOT EXISTS
(
SELECT 1
FROM #PrimeNumbers divisor
WHERE divisor.Number < (@Tested / 2) AND @Tested % divisor.Number = 0
);

SET @Tested = @Tested + 2;
END;

SELECT Number
FROM #PrimeNumbers
ORDER BY Number;

@nibor try using @highlight (not sure if it supports SQL syntax, but worth a shot)

@highlight

CREATE TABLE #PrimeNumbers (Number INT NOT NULL);

-- Insert 2 as first prime number
INSERT #PrimeNumbers (Number)
VALUES
(2);

-- Add a primary key
ALTER TABLE #PrimeNumbers
ADD CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED (Number);

DECLARE @Tested INT = 3;
DECLARE @MaxTested INT = 1000000;

WHILE @Tested < @MaxTested
BEGIN
INSERT #PrimeNumbers (Number)
SELECT @Tested
-- @Tested is a prime number if it has no prime factors with value less than half of itself
WHERE NOT EXISTS
(
SELECT 1
FROM #PrimeNumbers divisor
WHERE divisor.Number < (@Tested / 2) AND @Tested % divisor.Number = 0
);

SET @Tested = @Tested + 2;
END;

SELECT Number
FROM #PrimeNumbers
ORDER BY Number;

DROP TABLE #PrimeNumbers