en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
יול2

Written by: ronen ariely
02/07/2020 17:32 RssIcon

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
    )