Someone could make a case that you should store currency as MONEY, but CONVERT it to DECIMAL before doing calculations.
Calculation should not be totally dependent on storage type.
It is always good practice to explicitly convert your data to the desired type BEFORE using it in calculation, AND it saves a little storage.
To borrow from @SQLMenace's example:
DECLARE
--Just to drive the point, I'll use SMALLMONEY (4 bytes)
@mon1 SMALLMONEY,
@mon2 SMALLMONEY,
@mon3 SMALLMONEY,
@mon4 SMALLMONEY,
--This is the smallest DECIMAL that will hold this calculation (5 bytes)
@num1 DECIMAL(9,4),
@num2 DECIMAL(9,4),
@num3 DECIMAL(9,4),
@num4 DECIMAL(9,4)
SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000
--Convert it to decimal before calculation!
SET @mon4 = CONVERT(DECIMAL(19,4),@mon1)/
CONVERT(DECIMAL(19,4),@mon2)*
CONVERT(DECIMAL(19,4),@mon3)
SET @num4 = @num1/@num2*@num3
--Notice I didn't convert @mon4 from SMALLMONEY before presentation.
--It is the appropriate data type for this presentation.
SELECT @mon4 AS moneyresult,
@num4 AS numericresult
Output:
2949.8525 2949.8525
The same!
Sure, it's not as clean, but you can take care of that with a little formatting.
To add to the point, try swapping the SELECT out for this:
SELECT
--I added a zero to @mon3 and @num3
@mon1 = 101.5, @mon2 = 339.253, @mon3 = 100000,
@num1 = 101.5, @num2 = 339.253, @num3 = 100000
Output:
Msg 8115, Level 16, State 8, Line 13
Arithmetic overflow error converting int to data type numeric.
DECIMAL(9,4) cannot handle this number size, but SMALLMONEY can.
DECIMAL(10,4) (9 bytes) is the actual equivalent to SMALLMONEY (4 bytes).
To agree with @GerardONeill, in most circumstances you are not saving much space, but I do see that a case could be made that SMALLMONEY (4 bytes) being one byte less than DECIMAL(9,4) (5 bytes) when scaled up to a trillion values saves you a terabyte. (Where would you have a trillion records in small values? Did I hear someone say "microtransactions?") And furthermore, DECIMAL(9,4) does not store as many numbers! DECIMAL(10,4) jumps up to 9 bytes, and scaled up to a trillion values SMALLMONEY saves you 5 terabytes.
NOTE: If you compare MONEY (8 bytes) with DECIMAL(19,4) (9
bytes). One byte less saves a terabyte once you reach a trillion,
but in this case DECIMAL(19,4) gives you two more zeros if you add
them to @mon3 and @num3 in the SELECT above. So it all really
depends on how much you need to store.
Storage is cheep nowadays, so I'm not saying it's a strong case, but one could make the case and it is worth noting.
To summarize the value differences:
To REPLACE `SMALLMONEY (4 bytes) you would have to choose between
DECIMAL(9,4) (5 bytes) and sacrifice range of values ORDECIMAL(10,4) (9 bytes) for the same range but at more than double the storage used.
To summarize the argument, here are a few principles to follow:
MONEY is for presentation, not calculationDECIMAL(n,n) should be used for precise calculationsCONVERT your data before calculating with it.
That last one may just be a programmer thing, but being deliberate with your data types at the point of use (NOT JUST the point of storage) is important.
For instance, let's mess with @SQLMenace's answer again:
select t1.index_id,t2.index_id,(avg(t1.monret*t2.monret)
-(avg(t1.monret) * avg(t2.monret)))
/((sqrt(avg(square(t1.monret)) - square(avg(t1.monret))))
*(sqrt(avg(square(t2.monret)) - square(avg(t2.monret))))),
current_timestamp,@MaxDate
from Table1 t1 join Table1 t2 on t1.Date = traDate
group by t1.index_id,t2.index_id
What data types are being used in those calculations?
I HAVE NO IDEA
...except he did imply they were all DECIMAL
...BUT WERE THEY???
You can probably see where I'm going with this. From the code maintenance perspective, I personally prefer to have everything I need to know about the data I'm working with directly in front of me.
AND THEN
If you're already going to be converting the data into the type you need for calculation, why store it in a larger type?
Anyway, food for thought.
DECIMAL(19, 4)is a popular choice check this also check here World Currency Formats to decide how many decimal places to use , hope helps. – Orsino