Tsql – T-SQL Decimal Division Accuracy

precisiontsql

Does anyone know why, using SQLServer 2005

SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,9),12499999.9999)

gives me 11.74438969709659,

but when I increase the decimal places on the denominator to 15, I get a less accurate answer:

SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,15),12499999.9999)

give me 11.74438969

Best Solution

For multiplication we simply add the number of decimal places in each argument together (using pen and paper) to work out output dec places.

But division just blows your head apart. I'm off to lie down now.

In SQL terms though, it's exactly as expected.

--Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
--Scale = max(6, s1 + p2 + 1)

--Scale = 15 + 38 + 1 = 54
--Precision = 30 - 15 + 9 + 54 = 72
--Max P = 38, P & S are linked, so (72,54) -> (38,20)
--So, we have 38,20 output (but we don use 20 d.p. for this sum) = 11.74438969709659
SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,9),12499999.9999)


--Scale = 15 + 38 + 1 = 54
--Precision = 30 - 15 + 15 + 54 = 84
--Max P = 38, P & S are linked, so (84,54) -> (38,8)
--So, we have 38,8 output = 11.74438969
SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,15),12499999.9999)

You can do the same math if follow this rule too, if you treat each number pair as

  • 146804871.212533000000000 and 12499999.999900000
  • 146804871.212533000000000 and 12499999.999900000000000
Related Question