1

I've got a set of stored procedures which do a set of calculations, however I seem to be losing precision when using decimal types.

I'm expecting 0.0493318673793 for the calculation below - instead I'm getting 0.049331. I've tested out different data types, and the accuracy I'm after I'm only getting when using floats (which I've read should be avoided).

--Representative of SP
SELECT CAST(4000000.00 AS MONEY)/CAST(81083490.50 AS DECIMAL(34,26))
--0.049331

--Using greater decimal precision
SELECT CAST(4000000.00 AS MONEY)/CAST(81083490.50 AS DECIMAL(38,16))
--0.0493318

--Using Float
SELECT CAST(4000000.00 AS MONEY)/CAST(81083490.50 AS FLOAT)
--0.0493318673793403

Are floats really that bad? If so, what alternatives do I have?

Exploring on Hamlets note below. Why do the following below provide different outputs?

 SELECT CAST(4000000.00 AS MONEY)/CAST(81083490.50 AS DECIMAL(38,16))
 --0.0493318

 SELECT CAST(4000000.00 AS MONEY)/CAST(81083490.50 AS DECIMAL(38,3))
 --0.04933186737934031096
stats101
  • 1,837
  • 9
  • 32
  • 50
  • What is the actual range of numbers you need to deal with? – Martin Smith Jan 14 '13 at 13:22
  • 1
    Examine this two queries: `SELECT CAST(4000000.00 AS MONEY)/CAST(81083490.50 AS DECIMAL(38))` and `SELECT CAST(1 AS DECIMAL(38, 37))/CAST(81083490.50 AS DECIMAL(38, 2)), 1/CAST(81083490.50 AS DECIMAL(38, 2))` – Hamlet Hakobyan Jan 14 '13 at 13:33
  • @stats101 `float` seems to give you the results you need. So what's the issue? – bonCodigo Jan 14 '13 at 13:41
  • @MartinSmith I need to get the decimal number to the lowest level of granularity as I proceed to multiply it across a few million rows. The loss of accuracy means that the aggregate value can change drastically. – stats101 Jan 14 '13 at 14:37
  • Note that 32-bit floats cannot store 81803490.50. In that range a 32-bit float with its 24-bit mantissa can only store multiples of four. i.e. 1.x times 2^26. If by 'float' you mean 'double' (64-bit binary floating point) then that number can be exactly stored, but math on it may be inexact. See this article for more thoughts on precision: http://randomascii.wordpress.com/2012/03/08/float-precisionfrom-zero-to-100-digits-2/ – Bruce Dawson May 14 '14 at 02:47
  • "Precision, scale, and Length" of the T-SQL documentation documents and explains your issue (https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver16). Decimals and floats have different characteristics in terms of calculation speed and precision, and you will have to select the data type that best fits your use case. – mzuther Mar 20 '23 at 08:23

1 Answers1

0

This is only going to answer for your question "is float that bad?"...

Are you trying to ask someone to pay till 15th decimal point? ;) Because money only seems to have 4 decimal precision.

As for that famous thing about float if you are hesitant to use it then it makes sense due to this and this. But in your case float does what you need and float provides the prcision up to 15 decimal points. But it's an aproximate data type. So if you want to use float for money that means there's no much accuracy.

So in that case you are better off with decimal or money.

Reference:

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91