יול2
Written by:
ronen ariely
02/07/2020 17:32 
Best option is to create a numbers table in advanced
DROP TABLE IF EXISTS NumbersTbl
GO
CREATE TABLE NumbersTbl (N int, IsPrime bit)
GO
INSERT NumbersTbl(N)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_objects t1
CROSS JOIN sys.all_objects t2
GO
UPDATE n1 SET IsPrime = 1
FROM NumbersTbl n1
WHERE
n1.N < 1000
AND n1.N > 1
AND NOT EXISTS (
SELECT 1 FROM NumbersTbl n2
WHERE
n2.N > 1
AND n1.N > n2.N
AND n1.N % n2.N = 0
)
GO
CREATE CLUSTERED INDEX IX_NumbersTbl_N
ON NumbersTbl(N);
GO
CREATE NONCLUSTERED INDEX IX_NumbersTbl_IsPrime_N
ON NumbersTbl(IsPrime,N);
GO
the algorithm to find all Primes numbers
SELECT n1.N
FROM NumbersTbl n1
WHERE
n1.N < 1000
AND n1.N > 1
AND NOT EXISTS (
SELECT 1 FROM NumbersTbl n2
WHERE
n2.N > 1
AND n1.N > n2.N
AND n1.N % n2.N = 0
)