I got the fastest method in http://sqlblog.com/blogs/hugo_kornelis/archive/2006/09/23/Prime-numbers.aspx beat! Gotta give this blogger credit though because I'm using his idea of using modulo to eliminate the slow floating point math (indirect suggestion by Joe Celko's previous post). I'm pretty sure others can beat me though, as long as the fancier algorithms doesn't compromise index usage in the factor-side's use of the table of numbers.

Put up a new version (it'll get moderator-approved sometime monday). Still same the brute-force method, but hard-coded checks up to the first full data page of the table of numbers (622 for mine; put up to 625 in code) and limited the factor division tests to 627 to the square root of the candidate prime. This sped it up by 40% to one million . Primes from 1 to 250K might be slower. A smaller change eliminated the sort for the ROW_NUMBER (for nth prime queries), but it had only a .1 second impact.

The speed of this is solely because it is 100% set-based, and now limits data-access on the table of numbers (eliminating it for 84%, removing 1 logical I/O for the rest), not because of any fancy algorithm. Coding in C++ with the fancy algorithms and using SQLBulkCopy to insert the data would still be the fastest way.