Challenging SQL Server precision with Pi calculations.

Sometimes it is interesting just to play with SQL Server and hit its limits.

This time I've had some fun with getting closer to "Pi".
You might ask: -Why???

• We have wonderful Pi() function;
• If we need bigger precision, we can always get this number with up to one million digits after decimal point from a web site like this:"www.piday.org/million/";
• Usually 5 digits precision serves most of the tasks;

Yep. All of that is true, but all of these Pi long numbers came outside of SQL Server and it is very interesting how SQL Server can manage task of calculating Pi.

Lesson #1.

I'll start from very easy test:

SELECT 6167950454 / 1963319607

Two whole numbers, second one is INT and the first one looks like BIGINT because it is obviously bigger than the biggest integer number 2,147,483,647. As the result I expect to get whole number "3".

However, surprisingly I've got more precise result of 3.14159265358.
How came???
Lets look at the first number type:

DECLARE @N1 SQL_VARIANT = 6167950454;
SELECT [@N1] = @N1
       , [N1 BaseType] = SQL_VARIANT_PROPERTY(@N1, 'BaseType')
       , [N1 Precision] = SQL_VARIANT_PROPERTY(@N1, 'Precision')
       , [N1 Scale] = SQL_VARIANT_PROPERTY(@N1, 'Scale')

It goes like:
N1 BaseType : numeric
N1 Precision : 10
N1 Scale : 0

Interesting to know. SQL Server interprets integers higher than 2,147,483,647 as Numeric, not as BIGINT !!!

Will take a look at the result of the division:

SELECT 6167950454 / 1963319607
       , [N1 BaseType] = SQL_VARIANT_PROPERTY(6167950454/1963319607, 'BaseType')
       , [N1 Precision] = SQL_VARIANT_PROPERTY(6167950454/1963319607, 'Precision')
       , [N1 Scale] = SQL_VARIANT_PROPERTY(6167950454/1963319607, 'Scale')

It returns following:
It goes like:
N1 BaseType : numeric
N1 Precision : 21
N1 Scale : 11

How that happened? How SQL Server come up with that precision?
The answer is here: Precision, scale, and Length

From that link we can get 2 formulas to calculate precision and scale of our result:
R_Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
R_Scale = max(6, s1 + p2 + 1)

Where:
p1 = 10
s1 = 0
p2 = 10
s2 = 0

So, from it we get:
R_Precision = 10 - 0 + 0 max(6, 0 + 10 + 1) => 21
R_Scale = max(6, 0 + 10 + 1) => 11

Exact precision and scale we've got for the division's result!

Lesson #2.

In the previous division we've got 21 digits for precision and 11 digits for scale. Lets preset our numbers up to these parameters in order to get better precision:

DECLARE @N1 NUMERIC(21,11) = 6167950454, @N2 NUMERIC(21,11) = 1963319607;
SELECT @N1/@N2
       , SQL_VARIANT_PROPERTY(@N1/@N2, 'Precision') as Precision
       , SQL_VARIANT_PROPERTY(@N1/@N2, 'Scale') as Scale

Result : 3.14159265358979323
Precision : 38
Scale : 17

Result has much better precision: 17 digits after decimal point, however, accordingly to the formula from the Lesson #1 I'd expect to get a scale as high as 33 digits, but got twice less.
Why SQL did not give me more?
Read the article further: "The scale might be reduced using the following rules..."
The applicable rule for Scale reduction in this case is: "min(scale, 38 – (precision-scale))"
Accordingly to original formula:
R_Precision = 21 - 11 + 11 + (11 + 21 + 1) =>54
R_Scale = 11 + 21 + 1 => 33

And following reduction formula we get:
Reduced_Scale = 38 - (R_Precision - R_Scale) = 38 -  (54 - 33) => 17 !!!
Now we can explain how SQL Server reduces precision of the operation.

Lesson #3.

Now, when we know all formulas, can we increase precision of the result to the maximum and what is the maximum?

In order to get the best precision of the result we have to reduce scale of both numbers and precision of the first one. The best case scenario will give us following:

DECLARE @N1 NUMERIC(10,0) = 6167950454, @N2 NUMERIC(27,0) = 1963319607;
SELECT @N1/@N2
       , SQL_VARIANT_PROPERTY(@N1/@N2, 'Precision') as Precision
       , SQL_VARIANT_PROPERTY(@N1/@N2, 'Scale') as Scale
GO

That gives us incredible precision and scale:

Result : 3.1415926535897932383863775063*
Precision : 38
Scale : 28

*Disclosure: The real Pi number is closer to 3.141592653589793238462643383279502884187. That means even with 28 fractional digits we have only 18 matching digits. 

Accordingly to the formulas, scale of 28 for the division result is absolute limit.
Pretty sad, right? I've hoped it would be at least 37.
However, 28 digits it is not so bad.
The regular Pi() function returns much smaller number of fractional digits:

SELECT Pi()
       , SQL_VARIANT_PROPERTY(Pi(), 'Precision') as Precision
       , SQL_VARIANT_PROPERTY(Pi(), 'Scale') as Scale

Result : 3.14159265358979
Precision : 53
Scale : 0

Is not good, right?

We can improve it just a little bit:

DECLARE @N3 NUMERIC(38,38) = Pi()-3
SELECT @N3
       , SQL_VARIANT_PROPERTY(@N3, 'Precision') as Precision
       , SQL_VARIANT_PROPERTY(@N3, 'Scale') as Scale

Result : 3.1415926535897931000000000000000000000
Precision : 38
Scale : 37
This trick added just one more significant digit.

So, result of the research would be a fact that even though SQL Server can store decimal number with precision of 38 significant digits it can still support calculations only with maximum 28 digits precision.
And of cause, if you want to hit the limit, it won't be easy calculations.