When storing money values in SQL Server, I’ve always used Numeric/Decimal data types (or an Integer if I know I’m dealing only with whole dollar amounts). SQL Server has the money and smallmoney data types, and I know people that use them frequently. But I’ve always preferred the control from using Numeric, where I can set the specific number of digits to use. I vaguely remember reading about people claiming there were rounding issues with money types as well, but I never dug too deeply into those reasons.
I’m using Redgate SQL Prompt at my job, and it helps to analyze your T-SQL code. One of the things it warns against is using the Money or Small Money data types. This led me to dig a little deeper into those data types.

Positives For Money:

*) Clarity – It is obvious from the data type that the stored values are monetary.

*) You can use currency signs (Like $), commas and decimal points in a value, and can still successfully cast it to money:

SELECT CAST('$3,000.00' AS Money);

This statement will succeed, returning a value of 3000.00. Trying to cast the same string to Numeric will fail.

*) Storage space – The money types are stored as integer values, and often they will take up less storage space than a comparable decimal value.

DECLARE @MoneyValue MONEY = 3000000000.00;
DECLARE @DecimalValue DECIMAL(12,2) = 3000000000.00;

SELECT DATALENGTH(@MoneyValue) AS MoneyLength, DATALENGTH(@DecimalValue) AS DecimalLength;

Money values are 8 bytes, and this decimal value is 9 bytes.

DECLARE @MoneyValue SMALLMONEY = 200000.00;
DECLARE @DecimalValue DECIMAL(8,2) = 200000.00;

SELECT DATALENGTH(@MoneyValue) AS MoneyLength, DATALENGTH(@DecimalValue) AS DecimalLength;

Small Money values are 4 bytes, and this decimal value is 5 bytes.

There are some instances where the decimal value will take up less space, especially right above the smallmoney cutoff, so there isn’t always a savings.

Negatives For Money:

*) Control – Being able to define the precision and scale for numeric/decimal gives a lot more control to the programmer. This is the main reason I haven’t used the money types.

*) Not Standard SQL – A lot of other databases systems don’t support the money types. Oracle and MySQL doesn’t, although Postgres has a money type (but no smallmoney). Some people like to stick to standard data types (For porting to another database system, among other reasons).

*) Rounding
Here’s an example of rounding during division.

DECLARE @MoneyValue money = 25.99;
DECLARE @NumericValue numeric(4,2) = 25.99;
DECLARE @Divisor int = 48.0;

SELECT (@MoneyValue / @Divisor) as MoneyValue;
SELECT (@NumericValue / @Divisor) as NumericValue;

MoneyValue = 0.5414
NumericValue = 0.5414583333333

Granted, we have to go out to 5 decimal places to see the difference, but the rounding doesn’t work as I would expect.
This may not matter to some use cases, but it’s good to be aware of.

Wrap-up:
The rounding issues seem to be the main case with the Redgate suggestion to not use the money data types. Adding and subtracting aren’t affected by these issues, so simple use cases won’t be affected.
Personally, I plan to stick to the decimal type to store money values, but the money data types are probably fine to use in most scenarios. I’ve never had to deal with fractions of a cent (I’m in the US), or with multiplying or dividing money values

Links:

Mssqltips – SQL Money Data Type vs. SQL Decimal for Monetary Values in SQL Server

Sqlbot – SQL Money Data Type – Should I Use MONEY or DECIMAL?

SQL Authority – Interesting Observations Using Money Datatype/