Post date: May 09, 2014 11:24:36 AM
I didn't like this method too much because it involves going through the table row by row. And filling a table with a lot of data first and then deleting it. It seems like it would be a huge waste of time over a set-based solution. But you can't argue with an over 2X increase in speed. Sometimes the solution that seems less efficient is faster, so be willing to experiment.
-- Fill a table with numbers between 1 and 2 million.
CREATE TABLE #Integers (i int not null primary key)
INSERT INTO #Integers (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
; WITH Integers(i) AS
(
SELECT o.i +
(10 * d.i) +
(100 * h.i) +
(1000 * k.i) +
(10000 * k10.i) +
(100000 * k100.i) +
(1000000 * m.i)
FROM #Integers o
CROSS JOIN #Integers d
CROSS JOIN #Integers h
CROSS JOIN #Integers k
CROSS JOIN #Integers k10
CROSS JOIN #Integers k100
CROSS JOIN #Integers m
WHERE m.i < 2
)
INSERT INTO #Integers (i)
SELECT i
FROM Integers
WHERE i >= 10
-- Delete 1 and 0
DELETE FROM #Integers WHERE i < 2
-- Delete evens, not counting 2
DELETE FROM #Integers WHERE i % 2 = 0 AND i > 2
DECLARE @num int = 2
-- Go through the table row by row.
-- For each number, delete any multiples of that number greater than 1.
-- When you get to the square root of your max, only primes will be left.
-- (Any multiple above the square root will have been deleted already).
WHILE 1 = 1
BEGIN
SELECT @num = MIN(i)
FROM #Integers
WHERE i > @num
IF @num > SQRT(2000000)
BREAK
DELETE FROM #Integers
WHERE i % @num = 0
AND i > @num
END -- While
SELECT SUM(CONVERT(bigint,i))
FROM #Integers
DROP TABLE #Integers