Here’s the salient part of Louis’ post:
A friend blogger wrote an article about how to return prime numbers from SQL here: http://sqlservercode.blogspot.com/2006/09/return-all-78498-prime-numbers-between.html. Frankly, I was impressed by some of the responses I saw around about this topic. For example, this post: http://blogs.technet.com/wardpond/archive/2006/09/23/458591.aspx and these too: http://robfarley.blogspot.com/2006/09/primes.html, and http://robfarley.blogspot.com/2006/09/more-on-primes.html. But the answer I came up with is far faster than any of theirs for a production system (not nearly as cool, and takes far less brains, lucky for me.)
create table primeNumbers
i int primary key
insert into primeNumbers select 2
insert into primeNUmbers select 3
Once this code is done and stored, you never have to recalculate, unless someone comes up with some new prime numbers, which is pretty much unlikely. So if you need a prime number, the value is there and ready to use.
First of all, Louis, many thanks for your kind words.
While I agree that your alternative might run faster than Hugo‘s, Denis‘, or Rob‘s syntax, there’s still the small matter of generating the INSERT statements. The solution you’ve proposed requires previous knowledge of the prime numbers — how else can we generate the appropriate INSERTs?
Since the purpose of the exercise is to build this list, I don’t think we can use a syntax which presupposes that the list has already been generated. We’d essentially be rephrasing the problem, much like Kirk’s response to the Kobiyashi Maru test (the no-win scenario) in the first Star Trek movie.
So, from my perspective, straight INSERT statements don’t cut the mustard in the Prime Number challenge. If you feel differently, please let me know!
UPDATE (18 October 2006): Louis has updated his post to more clearly reflect his contention that we’d only ever build this list once. On this point, we are in complete agreement. Thanks for the clarification, Louis.