SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Appropriate Data Type


Appropriate Data Type

Author
Message
sam.dahl
sam.dahl
Mr or Mrs. 500
Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)

Group: General Forum Members
Points: 540 Visits: 887
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
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6304 Visits: 5478
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

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

How to post your question to get the best and quick help
demonfox
demonfox
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1711 Visits: 1192
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
Vedran Kesegic
Vedran Kesegic
SSC Eights!
SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)

Group: General Forum Members
Points: 940 Visits: 1266
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

sam.dahl
sam.dahl
Mr or Mrs. 500
Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)

Group: General Forum Members
Points: 540 Visits: 887
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.
Vedran Kesegic
Vedran Kesegic
SSC Eights!
SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)

Group: General Forum Members
Points: 940 Visits: 1266
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search