Similarities and Differences Between MONEY and SMALLMONEY Data Types

In SQL Server, each column, local variable, expression and parameter has a related data type.  A data type is an attribute that specifies the type of data that the object can hold, such as monetary data.  SQL Server has two data types that can hold monetary data, namely the MONEY data type and the SMALLMONEY data type.

The following article shows the similarities between the MONEY data type and the SMALLMONEY data type.

Similarities

• Both data types represent monetary or currency values.
• Both data types are accurate to a ten-thousandth of the monetary units they represent.
• Both data types does not need to be enclosed in single quotation marks (').
• A period is used to separate partial monetary units, like cents, from whole monetary units.

Differences

MONEY	SMALLMONEY
Range is from -922,337,203,685,477.5808 to 922,337,203,685,477.5807	Range is from -214,748.3648 to 214,748.3647
Uses 8 bytes of storage	Uses 4 bytes of storage

we can use currency symbol with Small money and money

 declare @m smallmoney =$23

 select @m

result

23.00

These data types can use any one of the following currency symbols.

Currency or monetary data does not need to be enclosed in single quotation marks ( ' ). It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value.

Converting money data

When you convert to money from integer data types, units are assumed to be in monetary units. For example, the integer value of 4 is converted to the money equivalent of 4 monetary units.

The following example converts smallmoney and money values to varchar and decimal data types, respectively.

SQL

DECLARE @mymoney_sm smallmoney = 3148.29,  
        @mymoney    money = 3148.29;  
SELECT  CAST(@mymoney_sm AS varchar) AS 'SM_MONEY varchar',  
        CAST(@mymoney AS decimal)    AS 'MONEY DECIMAL';