SQL floating-point precision limited to 6 digits

Question

I have the following set of calculations in excel that I want to be able to use in a stored procedure.

Excel

CellA: 45/448.2 = 0.100401606425703
CellB: 1-CellA = 0.899598393574297
CellC: 1-CellB = 0.100401606425703
CellD: CellC * 448.2 = 45.000000000000000

In SQL I am doing the following:

declare @a decimal(18,15) = 45/448.2  
declare @b decimal(18,15) = 1-@a
declare @c decimal(18,15) = 1-@b
declare @d decimal(18,15) = @c * 448.2

I have also tried running the calculation in one line

declare @e decimal(18,15) = (1-(1-(45/448.2)))*448.2

when I return the values SQL gives me the following:

@a: 0.100401000000000
@b: 0.899599000000000
@c: 0.100401000000000
@d: 44.999728200000000

@e: 44.999728200000000

I've tried adjusting the precision of the decimals in SQL but I nothing makes a difference, it only returns the first 6 digits of the decimal.

Does Excel do any optimization when running the formula?

Any ideas?

Answer 1

Even just your first line is enough to show the problem:

declare @a decimal(18,15) = 45/448.2  
print @a

gives

---------------------------------------
0.100401000000000

This is because of data types. When you say

448.2

it is (per the documentation) interpreted as a constant of type decimal, and also per the documentation,

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

So 448.2 is decimal(4,3). 45 is integer, which when combined with a decimal is treated as having precision of 10 and scale 0 . When we divide, the rules say

Operation     Result precision                        Result scale
e1 / e2       p1 - s1 + s2 + max(6, s1 + p2 + 1)      max(6, s1 + p2 + 1)

which in this case gives a result precision of 10 - 3 + 0 + max(6, 0 + 3 + 1) and scale of max(6, 0 + 3 + 1), which comes out to 13 and 6.

That result scale of 6 is why the result only has those six decimal places.

The way to fix it is to get your operands into an appropriate type before acting on them; for example, here are two ways:

Force a number to be treated as floating-point:

declare @a decimal(18,15) = 45/448.2e0  
select @a

---------------------------------------
0.100401606425703

Explicitly supply a decimal scale:

declare @a decimal(18,15) = 45/cast(448.2 as decimal(18,10))
select @a

---------------------------------------
0.100401606425703