יול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
)