Hi Jeff,

Here's some code so you can run it yourself. I'll add some run results in the next post.

--Inputs

DECLARE @MaxNumber INT

SET @MaxNumber = 5000000

--Preparation

SET NOCOUNT ON

DECLARE @Time DATETIME

SELECT @Time = GETDATE()

--Preparation - Numbers table

DECLARE @SqrtMaxNumber INT

SET @SqrtMaxNumber = SQRT(@MaxNumber)

SET ROWCOUNT @SqrtMaxNumber

CREATE TABLE #Numbers (i BIGINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, j BIGINT, x bit)

INSERT INTO #Numbers (x) SELECT NULL FROM syscomments c1, syscomments c2, syscomments c3, syscomments c4

SET ROWCOUNT 0

UPDATE #Numbers SET j = i*i

PRINT 'Checkpoint A: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VARCHAR(10)) + ' ms'

--Preparation - Put candidate primes into a Primes table

-- (integers which have an odd number of representations by certain quadratic forms)

CREATE TABLE #Primes (i BIGINT PRIMARY KEY CLUSTERED)

INSERT #Primes

SELECT 2 UNION ALL SELECT 3 UNION ALL

SELECT k FROM (

    SELECT k FROM (SELECT 4 * a.j + b.j AS k FROM #Numbers a, #Numbers b) c WHERE k <= @MaxNumber AND k % 12 IN (1, 5)

    UNION ALL

    SELECT k from (select 3 * a.j + b.j AS k FROM #Numbers a, #Numbers b) c WHERE k <= @MaxNumber AND k % 12 = 7

    UNION ALL

    SELECT k from (select 3 * a.j - b.j AS k FROM #Numbers a INNER JOIN #Numbers b ON a.i > b.i) c WHERE k <= @MaxNumber AND k % 12 = 11

) d GROUP BY k HAVING COUNT(*) IN (1, 3)

PRINT 'Checkpoint B: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VARCHAR(10)) + ' ms'

--Calculation - Eliminate composites by sieving

DECLARE @i BIGINT

SET @i = 5

WHILE @i * @i < @MaxNumber

BEGIN

    DELETE #Primes WHERE i > @i and i % @i = 0

    SELECT @i = min(i) FROM #Primes WHERE i > @i

    PRINT @i

END

--Show results

PRINT 'Finish: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VARCHAR(10)) + ' ms'

SELECT COUNT(*) AS 'Number of primes', MAX(i) AS 'Max prime', AVG(i) AS 'Average of primes' FROM #Primes

SELECT * FROM #Primes ORDER BY i

--Tidy up

DROP TABLE #Primes

DROP TABLE #Numbers