💎SQL Reloaded: Prime Numbers

I was just thinking about prime numbers and why people had such a fascination about them. In mysticism every number has a powerful meaning, a power coming from inside, and therewith attracts certain powers from exterior. It's strange and probably hard to understand the reduction of numbers between 1 and 9. Probably, each philosophy has a little truth in it. 

 
I was trying to find a nice article on number reduction, for example the one from sourceryforge could be useful. Last week I implemented a function which returns the reduction of any sequence, could be date or number. I was intending to apply it on prime numbers, but even if it can be observed certain similarities between different chains, it's difficult to believe this will lead anywhere. 

CREATE FUNCTION dbo.NumberReduction( 
   @text varchar(50)) 
RETURNS smallint 
/* Purpose: reduces to number a sequence of digits Parameters: @text varchar(50) - digits sequence 
   Notes: the function works with any sequnce of alphanumeric, not numeric values being ignored Sample: 
*/ 
BEGIN    
	DECLARE @index smallint = 1 
	DECLARE @total int = 0 

	WHILE (@index<=len(@text))   
	BEGIN          
		IF IsNumeric(Substring(@text, @index, 1)) = 1         
			SET @total = @total + Cast(Substring(@text, @index, 1) as smallint)  
		
		SET @index = @index+1    
	END    

	--if the number is not in 1..9 range, it calls further the NumberReduction for the new total    
	IF len(@total)>1       
		SET @total = dbo.NumberReduction(@total) 
	
	RETURN @total 
END 

-- testing the function
SELECT dbo.NumberReduction('11/17/2005') 
SELECT dbo.NumberReduction('1234') 
SELECT dbo.NumberReduction('1234 8993 90003 3456') 

Now, let's look at the number of 9592 primes found in the first 100.000 numbers:

-- number of primes in each thousands numbers 
SELECT PrimeNumber/1000+1 DataSet 
, count(*) NumberPrimeNumbers 
FROM PrimeNumbers 
GROUP BY PrimeNumber/1000+1
ORDER BY DataSet 

As it seems the maximum number of prime numbers is reached for the first thousand and decreases until is stabilizes somehow. It's more interesting what happens when the numbers are really big, however special techniques are used for testing such big numbers. The record is currently held by 2^136,279,841-1 with 41,024,320 digits (see Wikipedia). There are also interesting visualizations on prime numbers: see Ulam's spiral.

Here's a chart with the number of prime numbers and the table generated from the above script:

Thousands	Number Primes
1	169
2	135
3	127
4	120
5	119
6	114
7	117
8	107
9	110
10	112
11	106
12	103
13	109
14	105
15	102
16	108
17	98
18	104
19	94
20	104
21	98
22	104
23	100
24	104
25	94
26	98
27	101
28	94
29	98
30	92
31	95
32	92
33	106
34	100
35	94
36	92
37	99
38	94
39	90
40	96
41	88
42	101
43	102
44	85
45	96
46	86
47	90
48	95
49	89
50	98
51	89
52	97
53	89
54	92
55	90
56	93
57	99
58	91
59	90
60	94
61	88
62	87
63	88
64	93
65	80
66	98
67	84
68	99
69	80
70	81
71	98
72	95
73	90
74	83
75	92
76	91
77	83
78	95
79	84
80	91
81	88
82	92
83	89
84	84
85	87
86	85
87	88
88	93
89	76
90	94
91	89
92	85
93	97
94	86
95	87
96	95
97	84
98	82
99	87
100	87

See also plotting with prime numbers.

Happy coding!