Avoid the Money datatype in SQL for calculations
The Money datatype in SQL is only accurate to four decimal places.
So consider the following code using the money data type:
DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY
SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000
SET @mon4 = @mon1/@mon2*@mon3
SELECT @mon4 AS moneyresult
The output is 2949.0000
Now lets look at the same code using the decimal datatype:
DECLARE
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)
SELECT
@num1 = 100, @num2 = 339, @num3 = 10000
SET @num4 = @num1/@num2*@num3
@num4 AS numericresult
The output is 2949.8525
If this were real code in a financial system we would have a discrepancy of 0.8525 to try and justify to any auditors and the business as a whole. So when designing a database schema for any financial systems always use the decimal data type, or cast to decimal datatypes when performing calculations