...
The ranges of the two types have been discussed already, but there is another consideration:
-- Result is $1.0000 typed as smallmoney
DECLARE @credit smallmoney = $1.99;
SELECT @credit / 10000 * 10000;
GO
-- Result is 1.9900000000 typed as decimal (21, 10)
DECLARE @credit decimal(9,4) = 1.99;
SELECT @credit / 10000 * 10000;
...
In the above example by Paul White, you can see where the rounding happens and how it can potentially cause issues.
So, If you work with monetary datatypes you should understand how SQL works with them and when rounding is performed. Try this one:
-- Result is $1.99 typed as smallmoney
DECLARE @credit smallmoney = $1.99;
SELECT @credit * 10000 / 10000;
The reason it works fine for decimal is that SQL converts result of decimal(9,4) division by integer to decimal(21,10)
Check this one:
SELECT 1.99 / 10000.00
SELECT 1.99 / 10000.0
SELECT 1.99 / 10000
SELECT 1.9900 / 10000.00
SELECT 1.9900 / 10000.0
SELECT 1.9900 / 10000
So, I do prefer working with decimals as there are less thing to worry about, but sometimes you stuck with what you have. So, you should know implications and ways around.
😉