terry999 (9/25/2012)
smallmoney type takes up to 4 bytesdecimal(9,4) can only store 21474.3647 but use one more byte
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;
This may or may not be important to you, but the characteristics of the data type might be more interesting than saving a byte here or there. Indeed, there may be no byte to save if row compression is active, or if the extra byte per row does not result in at least one row having to be stored on a separate data page.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi