October 11, 2012 at 1:39 am
I'm looking for some second opinions on the choice of data types for some financial reporting.
Two transactional sources are being combined and I would like the destination table to store the relevant measures (it's a dwh implementation but this is more of a general question) let's say Quantity and SalesValue as numeric data types (we've seen the result of floats elsewhere and I'd prefer to store my data more precisely).
One of the source systems is fairly consistent in its use of the MONEY data type for SalesValue data, which is good. But it does use float for the Quantity data (the product can be liquid and therefore have decimal quantity).
The other source system is a Pervasive database which uses a data type that translates to SQL_DOUBLE (size 8) in ODBC terms, and I understand that this is best represented in SQL Server as a float.
At the moment the destination table uses floats (the inaccuracy is a minor annoyance), I'm adding a third source which is a duplicate of the first and I'm contemplating if it is worth the effort of converting the SalesValue data (if not the Quantity as well) to an appropriate numeric type.
Any thoughts would be welcome.
Thanks,
Sam
October 11, 2012 at 2:32 am
Float is a very bad choice for monetary values as it's approximate numeric.
I would recommend Decimal which is precise (exact numeric).
You can use Money too, if you brave enough, but be aware of how it behaves in calculations:
declare @m money
declare @d decimal(10,4)
select @m = 19.34, @d = 19.34
select (@m / 1000)*1000 as [using money], (@d / 1000)*1000 as [using decimal]
Ops... :w00t:
October 11, 2012 at 5:25 am
Eugene Elutin (10/11/2012)
Float is a very bad choice for monetary values as it's approximate numeric.I would recommend Decimal which is precise (exact numeric).
You can use Money too, if you brave enough, but be aware of how it behaves in calculations:
declare @m money
declare @d decimal(10,4)
select @m = 19.34, @d = 19.34
select (@m / 1000)*1000 as [using money], (@d / 1000)*1000 as [using decimal]
Ops... :w00t:
Yes u r right ..but the choice may depend on the usage of data ...same data used in money transaction may considers all precision where for research and analysis , it may not be fruitful to keep all the precision....
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
October 11, 2012 at 6:25 am
Look here about exact numerics and approximate numerics:
http://msdn.microsoft.com/en-us/library/ms187752.aspx
You definitely do not want to use approximate numerics in a financial application.
For storage of values use the type that can store all the digits and decimals you need, without rounding them. It can be money, or decimal(25, 13) or int or whatever data fits in without change.
For internal calculations you should use most precise type available. When done, convert it to type you will store it.
I recommend you this for internal calculations:
1) Use DECIMAL(25, 13) for all operands. It is a "magic" type. It is out of the scope to here explain why, it require understanding of microsoft's rules of retaining precision/scale after certain math operations. I'll write blog about it someday.
2) If you have three or more operations, subresult of each operation should be cast-ed to DECIMAL(25, 13). Looks ugly, but guaranties precision.
3) Do operations that increase result first, and then the ones that decrease it. For example "X*Z/Y" is better than "X/Y*Z". You might expect the same result, but it is not the case if subresult trimms decimals because they are gone out of scale.
That will guarantee you retaining precision of 13 decimals throughout entire your calculation process.
October 11, 2012 at 5:28 pm
Thanks to everyone for their input.
I'm happy to see that you're all recommending decimal which is what i meant by 'numeric'. I thought the two were interchangeable?
Vedran Kesegic (10/11/2012)
Look here about exact numerics and approximate numerics:http://msdn.microsoft.com/en-us/library/ms187752.aspx
You definitely do not want to use approximate numerics in a financial application.
For storage of values use the type that can store all the digits and decimals you need, without rounding them. It can be money, or decimal(25, 13) or int or whatever data fits in without change.
For internal calculations you should use most precise type available. When done, convert it to type you will store it.
I recommend you this for internal calculations:
1) Use DECIMAL(25, 13) for all operands. It is a "magic" type. It is out of the scope to here explain why, it require understanding of microsoft's rules of retaining precision/scale after certain math operations. I'll write blog about it someday.
2) If you have three or more operations, subresult of each operation should be cast-ed to DECIMAL(25, 13). Looks ugly, but guaranties precision.
3) Do operations that increase result first, and then the ones that decrease it. For example "X*Z/Y" is better than "X/Y*Z". You might expect the same result, but it is not the case if subresult trimms decimals because they are gone out of scale.
That will guarantee you retaining precision of 13 decimals throughout entire your calculation process.
Good to know, thanks. I'm aware of microsoft's rules but I still have to look them up when I need to remember them.
Based on your link and my own googling, I should be able to convert incoming floats (I have no control over the source systems, unfortunately) to decimals without any further loss of precision. Is that right? It's only converting decimals to floats that loses accuracy?
It seemed odd to me that one source uses floats for everything and the other even just uses it for quantities.
October 12, 2012 at 1:01 am
Decimal and numeric are functionally the same.
Numeric came first, and when SQL/92 standard came, they added decimal as a synonym to comply more with the standard sql type names.
Float has a wider order of magnitude (1.79E+308 to 2.23E-308). E.g. if you deal with astronomical figures together with microscopic, and it is not mandatory to be absolute precise, you probably want to use float.
By "microsoft rules" I ment the formulas for resulting precision and scale of each mathematical operator:
http://msdn.microsoft.com/en-us/library/ms190476.aspx
E.g. for dividing, the precision of the result is "p1 - s1 + s2 + max(6, s1 + p2 + 1)", and scale of the result is "max(6, s1 + p2 + 1)". Each mathematical operator has different formula, e.g. multiplication has other formulas.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply