Prime Number Table Generator (new ver crunches to 1 Mil in <10.5 sec!)

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

--Create Admin schema if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='Admin') EXECUTE ('CREATE SCHEMA Admin')

IF OBJECT_ID('Admin.Util_GeneratePrimes', 'P') IS NOT NULL DROP PROCEDURE Admin.Util_GeneratePrimes
GO

/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_GeneratePrimes
Prime Generator
By Jesse Roberge - YeshuaAgapao@Yahoo.com

Generates prime numbers and puts them into a table for future reference by adhoc queries and database and application code.
Uses a table of numbers to enable computation with a set-based insert..select.
If you don't have a table of numbers, you can use one of my generators to set one up - http://www.sqlservercentral.com/Authors/Scripts/Jesse_Roberge/13538/
You may have to tweak this code to point to where you ultimately have/place your table of numbers.
a recursive-CTE fn_numbers is faster than a table of numbers only with <1000 numbers.
Utilizing a table numbers with 386,884 numbers, it generates 61,924 prime numbers (772,517 max) in 7.2 seconds on my Dell Inspiron M170 laptop (single-core 2.0GHZ centrino),
also generates Strong, Sophhie, and safe flags (1.5 additional seconds).
The Sophie flag only works on about first half of the prime numbers generated
if 60,000 priimes are generated, about 30,000th-60,000th primes will not have the sophie flag set even if they are sophie.

Required Input Parameters
none

Optional Input Parameters
@MinPrime=1The minimum prime value to generate.  You will usually want this at 1
@MaxPrime=2097152The maximum prime value to generate.
The default value should use all of the values for most peoples' table of numbers
(622^2=386884 for mine; to completely fill 2-level deep clustered index).

Usage:
EXECUTE Admin.Util_GeneratePrimes @MinPrime=1, @MaxPrime=65536

Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, proprietarize modifications, or prohibit copying & re-distribution of this script/proc.

This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as
    published by the Free Software Foundation, either version 3 of the
    License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
**/
CREATE PROCEDURE Admin.Util_GeneratePrimes
@MinPrime int=1,
@MaxPrime int=2097152
AS

DECLARE @MinPrime2 int
SELECT @MinPrime2=CASE WHEN @MinPrime<3 THEN 3 ELSE @MinPrime END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
--Schema

IF OBJECT_ID ('Global.Global.Primes') IS NOT NULL DROP TABLE Global.Global.Primes

--Deistination Table (can be a changed to a permanent table)
CREATE TABLE Global.Global.Primes
(
RowNumber int NOT NULL,
NumberFloat Numeric(19,6),
NumberInt int NOT NULL,
Strong bit NOT NULL,
Sophie bit NOT NULL,
Safe bit NOT NULL,
CONSTRAINT PK_C_IX__Global__Primes__NumberInt PRiMARY KEY CLUSTERED (NumberInt)
)

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
--Populate Primes

INSERT INTO Global.Global.Primes (RowNumber, NumberFloat, NumberInt, Strong, Sophie, Safe)
SELECT
CASE WHEN @MinPrime<3 AND NumberInt>2 THEN RowNumber+1 ELSE RowNumber END AS RowNumber,
NumberFloat, NumberInt, 0, 0, 0
FROM
(
--Get candidate prime numbers
SELECT
ROW_NUMBER() OVER (ORDER BY Prime.PK_CountID) AS RowNumber,
Prime.NumberFloat, Prime.NumberInt
FROM
(
--Numbers to test; even numbers automatically fail; This method effectively doubles the number of numbers in the table of numbers
SELECT PK_CountID, CONVERT(decimal(19,6), PK_CountID*2+1) AS NumberFloat, PK_CountID*2+1 AS NumberInt, POWER(PK_CountID*2+1, 0.5) AS NumberSqrt
FROM Global.Global.Counter AS Prime
WHERE PK_CountID BETWEEN (@MinPrime2-1)/2 AND (@MaxPrime-1)/2
) AS Prime
WHERE
--Do the first data page of the table of numbers hard-coded; odd numbers only (even numbers never divide into odd)
--eliminates data access to the table of numbers for divisors<627 and reduces logical reads by one for the rest.
(Prime.NumberInt%3>0 OR Prime.NumberInt=3) AND
(Prime.NumberInt%5>0 OR Prime.NumberInt=5) AND
(Prime.NumberInt%7>0 OR Prime.NumberInt=7) AND
(Prime.NumberInt%9>0 OR Prime.NumberInt=9) AND
(Prime.NumberInt%11>0 OR Prime.NumberInt=11) AND
(Prime.NumberInt%13>0 OR Prime.NumberInt=13) AND
(Prime.NumberInt%15>0 OR Prime.NumberInt=15) AND
(Prime.NumberInt%17>0 OR Prime.NumberInt=17) AND
(Prime.NumberInt%19>0 OR Prime.NumberInt=19) AND
(Prime.NumberInt%21>0 OR Prime.NumberInt=21) AND
(Prime.NumberInt%23>0 OR Prime.NumberInt=23) AND
(Prime.NumberInt%25>0 OR Prime.NumberInt=25) AND
(Prime.NumberInt%27>0 OR Prime.NumberInt=27) AND
(Prime.NumberInt%29>0 OR Prime.NumberInt=29) AND
(Prime.NumberInt%31>0 OR Prime.NumberInt=31) AND
(Prime.NumberInt%33>0 OR Prime.NumberInt=33) AND
(Prime.NumberInt%35>0 OR Prime.NumberInt=35) AND
(Prime.NumberInt%37>0 OR Prime.NumberInt=37) AND
(Prime.NumberInt%39>0 OR Prime.NumberInt=39) AND
(Prime.NumberInt%41>0 OR Prime.NumberInt=41) AND
(Prime.NumberInt%43>0 OR Prime.NumberInt=43) AND
(Prime.NumberInt%45>0 OR Prime.NumberInt=45) AND
(Prime.NumberInt%47>0 OR Prime.NumberInt=47) AND
(Prime.NumberInt%49>0 OR Prime.NumberInt=49) AND
(Prime.NumberInt%51>0 OR Prime.NumberInt=51) AND
(Prime.NumberInt%53>0 OR Prime.NumberInt=53) AND
(Prime.NumberInt%55>0 OR Prime.NumberInt=55) AND
(Prime.NumberInt%57>0 OR Prime.NumberInt=57) AND
(Prime.NumberInt%59>0 OR Prime.NumberInt=59) AND
(Prime.NumberInt%61>0 OR Prime.NumberInt=61) AND
(Prime.NumberInt%63>0 OR Prime.NumberInt=63) AND
(Prime.NumberInt%65>0 OR Prime.NumberInt=65) AND
(Prime.NumberInt%67>0 OR Prime.NumberInt=67) AND
(Prime.NumberInt%69>0 OR Prime.NumberInt=69) AND
(Prime.NumberInt%71>0 OR Prime.NumberInt=71) AND
(Prime.NumberInt%73>0 OR Prime.NumberInt=73) AND
(Prime.NumberInt%75>0 OR Prime.NumberInt=75) AND
(Prime.NumberInt%77>0 OR Prime.NumberInt=77) AND
(Prime.NumberInt%79>0 OR Prime.NumberInt=79) AND
(Prime.NumberInt%81>0 OR Prime.NumberInt=81) AND
(Prime.NumberInt%83>0 OR Prime.NumberInt=83) AND
(Prime.NumberInt%85>0 OR Prime.NumberInt=85) AND
(Prime.NumberInt%87>0 OR Prime.NumberInt=87) AND
(Prime.NumberInt%89>0 OR Prime.NumberInt=89) AND
(Prime.NumberInt%91>0 OR Prime.NumberInt=91) AND
(Prime.NumberInt%93>0 OR Prime.NumberInt=93) AND
(Prime.NumberInt%95>0 OR Prime.NumberInt=95) AND
(Prime.NumberInt%97>0 OR Prime.NumberInt=97) AND
(Prime.NumberInt%99>0 OR Prime.NumberInt=99) AND
(Prime.NumberInt%101>0 OR Prime.NumberInt=101) AND
(Prime.NumberInt%103>0 OR Prime.NumberInt=103) AND
(Prime.NumberInt%105>0 OR Prime.NumberInt=105) AND
(Prime.NumberInt%107>0 OR Prime.NumberInt=107) AND
(Prime.NumberInt%109>0 OR Prime.NumberInt=109) AND
(Prime.NumberInt%111>0 OR Prime.NumberInt=111) AND
(Prime.NumberInt%113>0 OR Prime.NumberInt=113) AND
(Prime.NumberInt%115>0 OR Prime.NumberInt=115) AND
(Prime.NumberInt%117>0 OR Prime.NumberInt=117) AND
(Prime.NumberInt%119>0 OR Prime.NumberInt=119) AND
(Prime.NumberInt%121>0 OR Prime.NumberInt=121) AND
(Prime.NumberInt%123>0 OR Prime.NumberInt=123) AND
(Prime.NumberInt%125>0 OR Prime.NumberInt=125) AND
(Prime.NumberInt%127>0 OR Prime.NumberInt=127) AND
(Prime.NumberInt%129>0 OR Prime.NumberInt=129) AND
(Prime.NumberInt%131>0 OR Prime.NumberInt=131) AND
(Prime.NumberInt%133>0 OR Prime.NumberInt=133) AND
(Prime.NumberInt%135>0 OR Prime.NumberInt=135) AND
(Prime.NumberInt%137>0 OR Prime.NumberInt=137) AND
(Prime.NumberInt%139>0 OR Prime.NumberInt=139) AND
(Prime.NumberInt%141>0 OR Prime.NumberInt=141) AND
(Prime.NumberInt%143>0 OR Prime.NumberInt=143) AND
(Prime.NumberInt%145>0 OR Prime.NumberInt=145) AND
(Prime.NumberInt%147>0 OR Prime.NumberInt=147) AND
(Prime.NumberInt%149>0 OR Prime.NumberInt=149) AND
(Prime.NumberInt%151>0 OR Prime.NumberInt=151) AND
(Prime.NumberInt%153>0 OR Prime.NumberInt=153) AND
(Prime.NumberInt%155>0 OR Prime.NumberInt=155) AND
(Prime.NumberInt%157>0 OR Prime.NumberInt=157) AND
(Prime.NumberInt%159>0 OR Prime.NumberInt=159) AND
(Prime.NumberInt%161>0 OR Prime.NumberInt=161) AND
(Prime.NumberInt%163>0 OR Prime.NumberInt=163) AND
(Prime.NumberInt%165>0 OR Prime.NumberInt=165) AND
(Prime.NumberInt%167>0 OR Prime.NumberInt=167) AND
(Prime.NumberInt%169>0 OR Prime.NumberInt=169) AND
(Prime.NumberInt%171>0 OR Prime.NumberInt=171) AND
(Prime.NumberInt%173>0 OR Prime.NumberInt=173) AND
(Prime.NumberInt%175>0 OR Prime.NumberInt=175) AND
(Prime.NumberInt%177>0 OR Prime.NumberInt=177) AND
(Prime.NumberInt%179>0 OR Prime.NumberInt=179) AND
(Prime.NumberInt%181>0 OR Prime.NumberInt=181) AND
(Prime.NumberInt%183>0 OR Prime.NumberInt=183) AND
(Prime.NumberInt%185>0 OR Prime.NumberInt=185) AND
(Prime.NumberInt%187>0 OR Prime.NumberInt=187) AND
(Prime.NumberInt%189>0 OR Prime.NumberInt=189) AND
(Prime.NumberInt%191>0 OR Prime.NumberInt=191) AND
(Prime.NumberInt%193>0 OR Prime.NumberInt=193) AND
(Prime.NumberInt%195>0 OR Prime.NumberInt=195) AND
(Prime.NumberInt%197>0 OR Prime.NumberInt=197) AND
(Prime.NumberInt%199>0 OR Prime.NumberInt=199) AND
(Prime.NumberInt%201>0 OR Prime.NumberInt=201) AND
(Prime.NumberInt%203>0 OR Prime.NumberInt=203) AND
(Prime.NumberInt%205>0 OR Prime.NumberInt=205) AND
(Prime.NumberInt%207>0 OR Prime.NumberInt=207) AND
(Prime.NumberInt%209>0 OR Prime.NumberInt=209) AND
(Prime.NumberInt%211>0 OR Prime.NumberInt=211) AND
(Prime.NumberInt%213>0 OR Prime.NumberInt=213) AND
(Prime.NumberInt%215>0 OR Prime.NumberInt=215) AND
(Prime.NumberInt%217>0 OR Prime.NumberInt=217) AND
(Prime.NumberInt%219>0 OR Prime.NumberInt=219) AND
(Prime.NumberInt%221>0 OR Prime.NumberInt=221) AND
(Prime.NumberInt%223>0 OR Prime.NumberInt=223) AND
(Prime.NumberInt%225>0 OR Prime.NumberInt=225) AND
(Prime.NumberInt%227>0 OR Prime.NumberInt=227) AND
(Prime.NumberInt%229>0 OR Prime.NumberInt=229) AND
(Prime.NumberInt%231>0 OR Prime.NumberInt=231) AND
(Prime.NumberInt%233>0 OR Prime.NumberInt=233) AND
(Prime.NumberInt%235>0 OR Prime.NumberInt=235) AND
(Prime.NumberInt%237>0 OR Prime.NumberInt=237) AND
(Prime.NumberInt%239>0 OR Prime.NumberInt=239) AND
(Prime.NumberInt%241>0 OR Prime.NumberInt=241) AND
(Prime.NumberInt%243>0 OR Prime.NumberInt=243) AND
(Prime.NumberInt%245>0 OR Prime.NumberInt=245) AND
(Prime.NumberInt%247>0 OR Prime.NumberInt=247) AND
(Prime.NumberInt%249>0 OR Prime.NumberInt=249) AND
(Prime.NumberInt%251>0 OR Prime.NumberInt=251) AND
(Prime.NumberInt%253>0 OR Prime.NumberInt=253) AND
(Prime.NumberInt%255>0 OR Prime.NumberInt=255) AND
(Prime.NumberInt%257>0 OR Prime.NumberInt=257) AND
(Prime.NumberInt%259>0 OR Prime.NumberInt=259) AND
(Prime.NumberInt%261>0 OR Prime.NumberInt=261) AND
(Prime.NumberInt%263>0 OR Prime.NumberInt=263) AND
(Prime.NumberInt%265>0 OR Prime.NumberInt=265) AND
(Prime.NumberInt%267>0 OR Prime.NumberInt=267) AND
(Prime.NumberInt%269>0 OR Prime.NumberInt=269) AND
(Prime.NumberInt%271>0 OR Prime.NumberInt=271) AND
(Prime.NumberInt%273>0 OR Prime.NumberInt=273) AND
(Prime.NumberInt%275>0 OR Prime.NumberInt=275) AND
(Prime.NumberInt%277>0 OR Prime.NumberInt=277) AND
(Prime.NumberInt%279>0 OR Prime.NumberInt=279) AND
(Prime.NumberInt%281>0 OR Prime.NumberInt=281) AND
(Prime.NumberInt%283>0 OR Prime.NumberInt=283) AND
(Prime.NumberInt%285>0 OR Prime.NumberInt=285) AND
(Prime.NumberInt%287>0 OR Prime.NumberInt=287) AND
(Prime.NumberInt%289>0 OR Prime.NumberInt=289) AND
(Prime.NumberInt%291>0 OR Prime.NumberInt=291) AND
(Prime.NumberInt%293>0 OR Prime.NumberInt=293) AND
(Prime.NumberInt%295>0 OR Prime.NumberInt=295) AND
(Prime.NumberInt%297>0 OR Prime.NumberInt=297) AND
(Prime.NumberInt%299>0 OR Prime.NumberInt=299) AND
(Prime.NumberInt%301>0 OR Prime.NumberInt=301) AND
(Prime.NumberInt%303>0 OR Prime.NumberInt=303) AND
(Prime.NumberInt%305>0 OR Prime.NumberInt=305) AND
(Prime.NumberInt%307>0 OR Prime.NumberInt=307) AND
(Prime.NumberInt%309>0 OR Prime.NumberInt=309) AND
(Prime.NumberInt%311>0 OR Prime.NumberInt=311) AND
(Prime.NumberInt%313>0 OR Prime.NumberInt=313) AND
(Prime.NumberInt%315>0 OR Prime.NumberInt=315) AND
(Prime.NumberInt%317>0 OR Prime.NumberInt=317) AND
(Prime.NumberInt%319>0 OR Prime.NumberInt=319) AND
(Prime.NumberInt%321>0 OR Prime.NumberInt=321) AND
(Prime.NumberInt%323>0 OR Prime.NumberInt=323) AND
(Prime.NumberInt%325>0 OR Prime.NumberInt=325) AND
(Prime.NumberInt%327>0 OR Prime.NumberInt=327) AND
(Prime.NumberInt%329>0 OR Prime.NumberInt=329) AND
(Prime.NumberInt%331>0 OR Prime.NumberInt=331) AND
(Prime.NumberInt%333>0 OR Prime.NumberInt=333) AND
(Prime.NumberInt%335>0 OR Prime.NumberInt=335) AND
(Prime.NumberInt%337>0 OR Prime.NumberInt=337) AND
(Prime.NumberInt%339>0 OR Prime.NumberInt=339) AND
(Prime.NumberInt%341>0 OR Prime.NumberInt=341) AND
(Prime.NumberInt%343>0 OR Prime.NumberInt=343) AND
(Prime.NumberInt%345>0 OR Prime.NumberInt=345) AND
(Prime.NumberInt%347>0 OR Prime.NumberInt=347) AND
(Prime.NumberInt%349>0 OR Prime.NumberInt=349) AND
(Prime.NumberInt%351>0 OR Prime.NumberInt=351) AND
(Prime.NumberInt%353>0 OR Prime.NumberInt=353) AND
(Prime.NumberInt%355>0 OR Prime.NumberInt=355) AND
(Prime.NumberInt%357>0 OR Prime.NumberInt=357) AND
(Prime.NumberInt%359>0 OR Prime.NumberInt=359) AND
(Prime.NumberInt%361>0 OR Prime.NumberInt=361) AND
(Prime.NumberInt%363>0 OR Prime.NumberInt=363) AND
(Prime.NumberInt%365>0 OR Prime.NumberInt=365) AND
(Prime.NumberInt%367>0 OR Prime.NumberInt=367) AND
(Prime.NumberInt%369>0 OR Prime.NumberInt=369) AND
(Prime.NumberInt%371>0 OR Prime.NumberInt=371) AND
(Prime.NumberInt%373>0 OR Prime.NumberInt=373) AND
(Prime.NumberInt%375>0 OR Prime.NumberInt=375) AND
(Prime.NumberInt%377>0 OR Prime.NumberInt=377) AND
(Prime.NumberInt%379>0 OR Prime.NumberInt=379) AND
(Prime.NumberInt%381>0 OR Prime.NumberInt=381) AND
(Prime.NumberInt%383>0 OR Prime.NumberInt=383) AND
(Prime.NumberInt%385>0 OR Prime.NumberInt=385) AND
(Prime.NumberInt%387>0 OR Prime.NumberInt=387) AND
(Prime.NumberInt%389>0 OR Prime.NumberInt=389) AND
(Prime.NumberInt%391>0 OR Prime.NumberInt=391) AND
(Prime.NumberInt%393>0 OR Prime.NumberInt=393) AND
(Prime.NumberInt%395>0 OR Prime.NumberInt=395) AND
(Prime.NumberInt%397>0 OR Prime.NumberInt=397) AND
(Prime.NumberInt%399>0 OR Prime.NumberInt=399) AND
(Prime.NumberInt%401>0 OR Prime.NumberInt=401) AND
(Prime.NumberInt%403>0 OR Prime.NumberInt=403) AND
(Prime.NumberInt%405>0 OR Prime.NumberInt=405) AND
(Prime.NumberInt%407>0 OR Prime.NumberInt=407) AND
(Prime.NumberInt%409>0 OR Prime.NumberInt=409) AND
(Prime.NumberInt%411>0 OR Prime.NumberInt=411) AND
(Prime.NumberInt%413>0 OR Prime.NumberInt=413) AND
(Prime.NumberInt%415>0 OR Prime.NumberInt=415) AND
(Prime.NumberInt%417>0 OR Prime.NumberInt=417) AND
(Prime.NumberInt%419>0 OR Prime.NumberInt=419) AND
(Prime.NumberInt%421>0 OR Prime.NumberInt=421) AND
(Prime.NumberInt%423>0 OR Prime.NumberInt=423) AND
(Prime.NumberInt%425>0 OR Prime.NumberInt=425) AND
(Prime.NumberInt%427>0 OR Prime.NumberInt=427) AND
(Prime.NumberInt%429>0 OR Prime.NumberInt=429) AND
(Prime.NumberInt%431>0 OR Prime.NumberInt=431) AND
(Prime.NumberInt%433>0 OR Prime.NumberInt=433) AND
(Prime.NumberInt%435>0 OR Prime.NumberInt=435) AND
(Prime.NumberInt%437>0 OR Prime.NumberInt=437) AND
(Prime.NumberInt%439>0 OR Prime.NumberInt=439) AND
(Prime.NumberInt%441>0 OR Prime.NumberInt=441) AND
(Prime.NumberInt%443>0 OR Prime.NumberInt=443) AND
(Prime.NumberInt%445>0 OR Prime.NumberInt=445) AND
(Prime.NumberInt%447>0 OR Prime.NumberInt=447) AND
(Prime.NumberInt%449>0 OR Prime.NumberInt=449) AND
(Prime.NumberInt%451>0 OR Prime.NumberInt=451) AND
(Prime.NumberInt%453>0 OR Prime.NumberInt=453) AND
(Prime.NumberInt%455>0 OR Prime.NumberInt=455) AND
(Prime.NumberInt%457>0 OR Prime.NumberInt=457) AND
(Prime.NumberInt%459>0 OR Prime.NumberInt=459) AND
(Prime.NumberInt%461>0 OR Prime.NumberInt=461) AND
(Prime.NumberInt%463>0 OR Prime.NumberInt=463) AND
(Prime.NumberInt%465>0 OR Prime.NumberInt=465) AND
(Prime.NumberInt%467>0 OR Prime.NumberInt=467) AND
(Prime.NumberInt%469>0 OR Prime.NumberInt=469) AND
(Prime.NumberInt%471>0 OR Prime.NumberInt=471) AND
(Prime.NumberInt%473>0 OR Prime.NumberInt=473) AND
(Prime.NumberInt%475>0 OR Prime.NumberInt=475) AND
(Prime.NumberInt%477>0 OR Prime.NumberInt=477) AND
(Prime.NumberInt%479>0 OR Prime.NumberInt=479) AND
(Prime.NumberInt%481>0 OR Prime.NumberInt=481) AND
(Prime.NumberInt%483>0 OR Prime.NumberInt=483) AND
(Prime.NumberInt%485>0 OR Prime.NumberInt=485) AND
(Prime.NumberInt%487>0 OR Prime.NumberInt=487) AND
(Prime.NumberInt%489>0 OR Prime.NumberInt=489) AND
(Prime.NumberInt%491>0 OR Prime.NumberInt=491) AND
(Prime.NumberInt%493>0 OR Prime.NumberInt=493) AND
(Prime.NumberInt%495>0 OR Prime.NumberInt=495) AND
(Prime.NumberInt%497>0 OR Prime.NumberInt=497) AND
(Prime.NumberInt%499>0 OR Prime.NumberInt=499) AND
(Prime.NumberInt%501>0 OR Prime.NumberInt=501) AND
(Prime.NumberInt%503>0 OR Prime.NumberInt=503) AND
(Prime.NumberInt%505>0 OR Prime.NumberInt=505) AND
(Prime.NumberInt%507>0 OR Prime.NumberInt=507) AND
(Prime.NumberInt%509>0 OR Prime.NumberInt=509) AND
(Prime.NumberInt%511>0 OR Prime.NumberInt=511) AND
(Prime.NumberInt%513>0 OR Prime.NumberInt=513) AND
(Prime.NumberInt%515>0 OR Prime.NumberInt=515) AND
(Prime.NumberInt%517>0 OR Prime.NumberInt=517) AND
(Prime.NumberInt%519>0 OR Prime.NumberInt=519) AND
(Prime.NumberInt%521>0 OR Prime.NumberInt=521) AND
(Prime.NumberInt%523>0 OR Prime.NumberInt=523) AND
(Prime.NumberInt%525>0 OR Prime.NumberInt=525) AND
(Prime.NumberInt%527>0 OR Prime.NumberInt=527) AND
(Prime.NumberInt%529>0 OR Prime.NumberInt=529) AND
(Prime.NumberInt%531>0 OR Prime.NumberInt=531) AND
(Prime.NumberInt%533>0 OR Prime.NumberInt=533) AND
(Prime.NumberInt%535>0 OR Prime.NumberInt=535) AND
(Prime.NumberInt%537>0 OR Prime.NumberInt=537) AND
(Prime.NumberInt%539>0 OR Prime.NumberInt=539) AND
(Prime.NumberInt%541>0 OR Prime.NumberInt=541) AND
(Prime.NumberInt%543>0 OR Prime.NumberInt=543) AND
(Prime.NumberInt%545>0 OR Prime.NumberInt=545) AND
(Prime.NumberInt%547>0 OR Prime.NumberInt=547) AND
(Prime.NumberInt%549>0 OR Prime.NumberInt=549) AND
(Prime.NumberInt%551>0 OR Prime.NumberInt=551) AND
(Prime.NumberInt%553>0 OR Prime.NumberInt=553) AND
(Prime.NumberInt%555>0 OR Prime.NumberInt=555) AND
(Prime.NumberInt%557>0 OR Prime.NumberInt=557) AND
(Prime.NumberInt%559>0 OR Prime.NumberInt=559) AND
(Prime.NumberInt%561>0 OR Prime.NumberInt=561) AND
(Prime.NumberInt%563>0 OR Prime.NumberInt=563) AND
(Prime.NumberInt%565>0 OR Prime.NumberInt=565) AND
(Prime.NumberInt%567>0 OR Prime.NumberInt=567) AND
(Prime.NumberInt%569>0 OR Prime.NumberInt=569) AND
(Prime.NumberInt%571>0 OR Prime.NumberInt=571) AND
(Prime.NumberInt%573>0 OR Prime.NumberInt=573) AND
(Prime.NumberInt%575>0 OR Prime.NumberInt=575) AND
(Prime.NumberInt%577>0 OR Prime.NumberInt=577) AND
(Prime.NumberInt%579>0 OR Prime.NumberInt=579) AND
(Prime.NumberInt%581>0 OR Prime.NumberInt=581) AND
(Prime.NumberInt%583>0 OR Prime.NumberInt=583) AND
(Prime.NumberInt%585>0 OR Prime.NumberInt=585) AND
(Prime.NumberInt%587>0 OR Prime.NumberInt=587) AND
(Prime.NumberInt%589>0 OR Prime.NumberInt=589) AND
(Prime.NumberInt%591>0 OR Prime.NumberInt=591) AND
(Prime.NumberInt%593>0 OR Prime.NumberInt=593) AND
(Prime.NumberInt%595>0 OR Prime.NumberInt=595) AND
(Prime.NumberInt%597>0 OR Prime.NumberInt=597) AND
(Prime.NumberInt%599>0 OR Prime.NumberInt=599) AND
(Prime.NumberInt%601>0 OR Prime.NumberInt=601) AND
(Prime.NumberInt%603>0 OR Prime.NumberInt=603) AND
(Prime.NumberInt%605>0 OR Prime.NumberInt=605) AND
(Prime.NumberInt%607>0 OR Prime.NumberInt=607) AND
(Prime.NumberInt%609>0 OR Prime.NumberInt=609) AND
(Prime.NumberInt%611>0 OR Prime.NumberInt=611) AND
(Prime.NumberInt%613>0 OR Prime.NumberInt=613) AND
(Prime.NumberInt%615>0 OR Prime.NumberInt=615) AND
(Prime.NumberInt%617>0 OR Prime.NumberInt=617) AND
(Prime.NumberInt%619>0 OR Prime.NumberInt=619) AND
(Prime.NumberInt%621>0 OR Prime.NumberInt=621) AND
(Prime.NumberInt%623>0 OR Prime.NumberInt=623) AND
(Prime.NumberInt%625>0 OR Prime.NumberInt=625) AND
--Test the remaining candidate prime numbers
NOT EXISTS (
SELECT *
FROM
(
SELECT CONVERT(decimal(19,6), PK_CountID) AS NumberFloat, PK_CountID AS NumberInt
FROM Global.Global.Counter AS Factor
) AS Factor
WHERE
--Limit the test range to between 2 and PrimeTestNumber^0.5
Factor.NumberInt BETWEEN 627 AND Prime.NumberSqrt
--(Commented because it killed performance because because of the execution plan changing to include a TempDB spool) Even numbers never divide into odd numbers (no doubling trick needed here)
--AND PK_CountID % 2 = 1
--Test the divisibility (the first failure should stop the checking for the current outer row because of the EXISTS() semi-join)
AND Prime.NumberInt%Factor.NumberInt=0
)
UNION ALL
(SELECT 1,2,2 WHERE @MinPrime<3)
) AS Primes

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

CREATE UNIQUE NONCLUSTERED INDEX U_IX__Global__Primes__NumberFloat__RowNumber ON GLobal.Global.Primes (NumberFloat) INCLUDE (RowNumber) WITH (FILLFACTOR=100)
CREATE UNIQUE NONCLUSTERED INDEX U_IX__Global__Primes__RowNumber__NumberFloat ON GLobal.Global.Primes (RowNumber) INCLUDE (NumberFloat) WITH (FILLFACTOR=100)

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
--Update for Prime type flags

--Set sophie / safe / strong fields
UPDATE Global.Global.Primes SET
Sophie=CASE WHEN EXISTS (SELECT * FROM Global.Global.Primes AS PrimesSophie WHERE Primes.NumberInt*2+1=PrimesSophie.NumberInt) THEN 1 ELSE 0 END,
Safe=CASE WHEN EXISTS (SELECT * FROM Global.Global.Primes AS PrimesSafe WHERE (Primes.NumberInt-1)/2=PrimesSafe.NumberInt) THEN 1 ELSE 0 END,
Strong=CASE WHEN EXISTS (
SELECT *
FROM Global.Global.Primes AS PrimesStrong CROSS JOIN Global.Global.Primes AS PrimesStrong2
WHERE
Primes.RowNumber-1=PrimesStrong.RowNumber
AND Primes.RowNumber+1=PrimesStrong2.RowNumber
AND Primes.NumberFloat>(PrimesStrong.NumberFloat+PrimesStrong2.NumberFloat)/2.0
) THEN 1 ELSE 0 END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
--Test / Get primes that are sophie, safe, and strong

/*
--Test select
SELECT RowNumber, NumberInt, Strong, Sophie, Safe, NumberInt%4 AS Mod4
FROM Global.Global.Primes
WHERE Sophie=1 AND Safe=1 AND Strong=1
*/
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=