I got an usual request today in reference to writing SQL statements to find Prime & Perfect Numbers.

After a bit of clarification, (Primes & Perfect numbers less than 1 million)
I convinced them not to write an alogrithm in TSQL and that ideally you would have a table of Prime & Perfect numbers and just JOIN to those tables when needed.

"How do we fill the Prime table?", was the obvious next question.

This is surprisingly simple thanks to the Internet... Prime Site This sight has a list of Prime numbers...
A simply parse and a BCP and we have a 100,000 row Prime table.

But it did get me thinking.. If we had a Numbers table with numbers 1 to 1 million, could we write a SQL statement to find the Primes or Perfect Numbers?

I am going to stick with numbers below a 1000 for the solution..
And allow 2 exceptions, those being the primes 1 & 2!!!!
Below is the code for the numbers table (Only to 1000)
CREATE TABLE Numbers(Number INT NOT NULL PRIMARY KEY CLUSTERED)
GO
DECLARE @i INT
SET @i = 1
WHILE @i < 1001
BEGIN
    INSERT Numbers(Number) values (@i)
    SET @i = @i + 1
END
GO

Now for the SQL Stuff

--Prime Numbers
SELECT X.Number as Primes
FROM Numbers N CROSS JOIN Numbers X
WHERE X.Number%N.Number != 0 AND X.Number > 1
    AND N.Number > 0 AND N.Number < X.Number and X.Number%2 !=0
GROUP BY X.Number
HAVING (X.Number - Count(*)) = 2

–Perfect Numbers
SELECT X.Number as Perfect
FROM Numbers N CROSS JOIN Numbers X
WHERE X.Number%N.Number = 0 and X.Number > 1
   AND N.Number < X.Number AND N.Number > 0
GROUP BY X.Number
HAVING SUM(N.Number) = X.Number

There seems to be an uncanny likeness between the 2...

I am interested in all other non-cursor solutions..