PI is a very commonly used mathematical constant, in fact it is probably more famous than the equation E=MC2. We might not find a lot of use for it within the database community but it is a fascinating value to calculate and so we set out to do just that.
First we tried using the inbuilt function within SQL Server
This as you can see causes a problem since the value is accurate only to 14 decimal places and it you are familiar with Math you will know that the value of PI has been calculated up to a Trillion places. So we were nowhere close.
Naturally we are not aiming for a trillion places but we were hoping for more than 14 so the effort continued. Using the fractional notation
Starting with 22/7 which is the most commonly used we get the below value which is not even close in terms of accuracy
So we went on to 355/113, which is better than the 22/7 fraction but still not as good as our original result from using the inbuilt function
So we decided to go basic and simply calculate PI as the circumference divided by the diameter of a circle. Which brought us to the below screenshot which is the same accuracy as the value of PI derived from the function
So now we needed to go extreme and start using some proper Math. While there are many different series that approximate the value of Pi we found that they do not converge fast enough. Often after 100 iterations we are only accurate to 6 or 7 decimal places. The amount of computational overhead meant that we needed to find a series that converges faster. Enter Chudnovsky algorithm. This is the algo used to set the world record for Pi right now.
The equation is simple enough that we can code for it.
But first we needed a way to calculate factorials. Once we wrote the function to calculate factorials it became clear that this method is unsuitable within MS SQL Server since within k=2 we reach numbers that result in arithmetic overflow so the approach was abandoned for a less rigorous and easier to work with series called Nilakantha series.
This equation is easier to use
The code was implemented using a simple while loop to calculate each part of the above equation and pretty soon we were reaching accuracy up to 21 decimal places in a matter of seconds. So we went ahead and made the procedure a natively compiled store procedure to see if we can improve the execution time so that we can perform more iterations. In our final attempt we performed 1 Billion iterations which took 6 min and resulted in accuracy to 27 decimal places
The final output we got was
3.1415926535897932384626433831700000000
The more accurate value would be
3.14159265358979323846264338327950288419
The code we used is mentioned below
CREATE PROCEDURE ncsp_calculatepi
WITH NATIVE_COMPILATION
,SCHEMABINDING
,EXECUTE AS OWNER
AS
BEGIN
ATOMIC
WITH (
TRANSACTION ISOLATION LEVEL = SNAPSHOT
,LANGUAGE = N'us_english'
)
--Insert statements for the stored procedure here
DECLARE @id DECIMAL(38, 10) = 2
DECLARE @pi DECIMAL(38, 37) = 3
DECLARE @even DECIMAL(38, 37) = 1.00000000000000000000000000000
WHILE @id < 10000000000
BEGIN --
SELECT @even * (4.000000000000000000000000 / (@id * (@id + 1) * (@id + 2)))
SET @pi = @pi + @even * (4.000000000000000000000000000 / (@id * (@id + 1) * (@id + 2)))
SET @id = @id + 2
SET @even = @even * - 1
END
SELECT @pi AS Pi
END GO
Frankly beyond 10 million iterations there is not much gain in accuracy (26 decimal places) compared to execution time. After 10 Billion Iterations we didn’t see any improvement in accuracy.
The biggest challenge with calculating pi inside SQL SERVER has to be the limiting datatypes which prevent any kind of logic which will really provide accuracy in the range of hundreds of decimal places.
Please Consider Subscribing
