Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Appropriate Data Type Expand / Collapse
Author
Message
Posted Thursday, October 11, 2012 1:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 14, 2014 6:36 PM
Points: 367, Visits: 820
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
Post #1371248
Posted Thursday, October 11, 2012 2:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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...





_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1371286
Posted Thursday, October 11, 2012 5:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
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...



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
Post #1371375
Posted Thursday, October 11, 2012 6:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:57 AM
Points: 369, Visits: 1,208
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.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1371410
Posted Thursday, October 11, 2012 5:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 14, 2014 6:36 PM
Points: 367, Visits: 820
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.
Post #1371832
Posted Friday, October 12, 2012 1:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:57 AM
Points: 369, Visits: 1,208
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.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1371916
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse