Decimal versus Money and division
-
Hello...
Trying to store a number with 12 precision.
Example being 123.123456123456
This number is the result of arithmetic performed on two other numbers.
Both of these numbers are of the "Money" type.
Heres sample 1 using the money type:
DECLARE @X money
DECLARE @Y money
--DECLARE @X decimal(19, 4)
--DECLARE @Y decimal(19, 4)
DECLARE @Z decimal(19, 12)
SET @X = 869.26
SET @Y = 979.02
SET @Z = ROUND(((@X - @Y) / @Y), 12)
SELECT @Z
>>Result = -.112100000000
Heres sample 2 using the decimal type:
--DECLARE @X money
--DECLARE @Y money
DECLARE @X decimal(19, 4)
DECLARE @Y decimal(19, 4)
DECLARE @Z decimal(19, 12)
SET @X = 869.26
SET @Y = 979.02
SET @Z = ROUND(((@X - @Y) / @Y), 12)
SELECT @Z
>>Result = -.112112112112
If I take any data itme and makes it's type "Money" - SQL Server
shows it as:
Precision: 19
Scale: 4
Length: 8
So - if I have a Decimal of Precision 19 and Scale 4 - should they no behave the same?
Also - a decimal(19, 4) is 9 bytes in length whereas the money type is 8.
Any ideas on why this is happening ??
Yes - I am familiar with CAST and CONVERT...
Just want to know what's going on here... B
-
The only difference I can think of between the two data types is that the MONEY datatype will ALWAYS have a precise scale of 4 (meaning, always has 4 digits to the right of the decimal), whereas a DECIMAL(19, 4) may, or may not have 4 digits to the right of the decimal point...of course, I may be completely off the mark here. 🙂
-
Can only suspect it's by design. Operations on MONEY fields return a MONEY field. Operations on DECIMAL return DECIMAL. The difference is that DECIMAL operations will adjust the scale based on the scales of the various operands, whereas MONEY always has a maximum scale of 4.
Running the following:
DECLARE @Xm money
DECLARE @Ym money
DECLARE @Xd decimal(19, 4)
DECLARE @Yd decimal(19, 4)
SET @Xm = 869.26
SET @Ym = 979.02
SET @Xd = 869.26
SET @Yd = 979.02
SELECT SQL_VARIANT_PROPERTY ( ROUND(((@Xm - @Ym) / @Ym), 12), 'basetype' )
, SQL_VARIANT_PROPERTY ( ROUND(((@Xm - @Ym) / @Ym), 12), 'scale' )
SELECT SQL_VARIANT_PROPERTY ( ROUND(((@Xd - @Yd) / @Yd), 12), 'basetype' )
, SQL_VARIANT_PROPERTY ( ROUND(((@Xd - @Yd) / @Yd), 12), 'scale' )Returns:
MONEY 4
DECIMAL 18
Cheers,
- Mark -
Hello BillyWilly
the money type has an accuracy of a ten-thousandth of a monetary unit. That is 1.1234. Any numbers after these 4 digits are rounded. While I think, this accuracy is good enough for 'normal' calculations with small numbers and few step of calculations, it is definitely not enough when large numbers are involved or if there are many steps of calculations to perform. The highest accuracy you'll get with decimal type.
BTW, a quick check of SQL Server calculations with Excel is always a good idea.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks guys - I guess using CAST will just just be part of the game regardless.
I wish I could include pictures in here but here it goes...
If you open the SQL Server MMC Snap-in, go into a database and open a table by clicking on "Design Table" - you get a list of columns and their types.
Click on a column of the "Money" type and you'll see the precision and scale set to 19 and 4 respectively. Note that they are "greyed out" and cannot be edited since it's the Money type - but to me that states behind the covers that Money is a decimal of Precision 19 and scale 4. Also note that it's "Length" is 8.
Now create a decimal column - also of precision 19 and scale 4 and you'll see it's length is 9 bytes rather than 8.
No big deal - I'll just do some casting during division to get the 12 precision I want - just found this to be interesting.
- B
-
quote:
...you'll see it's length is 9 bytes rather than 8....As I mentioned in my first post, I believe SQL Server needs the extra byte to keep track of where the decimal place is in the true DECIMAL(19,4) data type, whereas the MONEY data type is always between the fourth and the fifth position from the right.
-
Hello BillyWilly,
quote:
No big deal - I'll just do some casting during division to get the 12 precision I want - just found this to be interesting.it is interesting!
For further information on decimal data type see
http://standards.ieee.org/reading/ieee/std_public/description/busarch/854-1987_desc.html or http://www2.hursley.ibm.com/decimal/decbits.html .
Hope this will feed your appetite for information
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
A close reading of SQL Server Books Online strongly implies that MONEY and SMALLMONEY are stored as "bigint" and "int" datatypes with an implied decimal point. This means that
(1) MONEY and SMALLMONEY will tend to be more compact on disk than DECIMAL, and
(2) User a5xo3z1 is correct in stating that rounding errors will be an issue when multiplying and dividing (but not for adding and subtracting).
(3) A numeric conversion will definitely take place if MONEY or SMALLMONEY are converted to DECIMAL.
Here is the quote from BOL. Note how the max and min values of MONEY and BIGINT line up exactly when the decimal point is removed. The same is true for SMALLMONEY and INT:
money
-----
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.
bigint
------
Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.
smallmoney
----------
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Storage size is 4 bytes.
int
---
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes.
-
This may be slightly off topic, but may be of some use. I spent many years programming for engineers. If they see the slightest discrepancy even in the sixth decimal position, they can start doubting the accuracy of your results.
A rule of thumb that has served me well; find out how many decimal positions the end user has on their calculator. Declare the variables for intermediate calculations with an additional four decimal positions. Round off the final answer to match the number of positions on the individual’s calculator.
-
If engineer A is only interested in the results of step II and engineer B is interested in the end results after N steps more on the results of II. Question, when the rest of the steps results are calculated do you use the rounded/approximated values or the full decimal value? In case of a bean counter the like to do spot checks
-
Ever been around with actuaries or top management?
quote:
This may be slightly off topic, but may be of some use. I spent many years programming for engineers. If they see the slightest discrepancy even in the sixth decimal position, they can start doubting the accuracy of your results.they'll discuss for hours on accuracy of decimal positions and don't realise that the whole number is totally wrong. I was told that numbers don't have to be accurate but plausible
Honestly, as happened some days ago while discussing on our rebudget.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply