Money & SmallMoney data types in SQL Server

SQL Server money data types are special data types that represent monetary or currency values. SQL Server comes with two variants of the money data type. One is smallmoney and the other one is money

Money

The money data type has fixed four digits after the decimal. It can handle −922,337, 203, 685,477.5808 to +922,337, 203, 685,477.5807 and occupies 8 bytes of storage

SmallMoney

The smallmoney data type has fixed four digits after the decimal. It can store numbers from −214,478.3648 to +214478.3647 and takes 4 bytes of storage.

Data TypeRange fromRange toMemory
smallmoney−214,478.3648+214478.36474 bytes
money−922,337, 203, 685,477.5808+922,337, 203, 685,477.58078 bytes

Money Vs Decimal

The Smallmoney looks equivalent to decimal(10,4) & Money is equivalent to decimal(19,4). But they are not the same

Money is stored as integer

The major difference is that the money is stored as integers. The Smallmoney is stored as int & Money is stored as BigInt. They are stored without decimal places. The decimal places are added only when you query and view the data. 

For Example

The Number 1234.00  is stored as 12340000, 5555.5555 is stored as 55555555

The decimal is stored with a decimal point and decimal digits

Money decimal places are fixed. 

The Money data type uses the fixed 4 fraction digits. In Decimal, you can set the number of fraction digits.

Loss of precision

Money sufferers from lose of precision as it is treated as an integer in mathematical operations. Especially, when we use it in operations involving multiplications & divisions.

The money stores up to 4 decimal places. If the result (even for intermediate results) of multiplication or division results in a value that has more than 4 decimal places, it will be round off.

The @num1 /10 in the following example results in 123.45678. The Money type discards the last decimal digit 8 and returns 123.4567 as the result.

While decimal(10,4) retains the precision.

The difference due to the fact that intermediate values of the decimal can maintain precision up to 38 digits.

Integers operations are faster

The mathematical operations like additions & subtractions are significantly faster because money is treated as integers.

Money Requires less storage

Money requires less storage compared to the decimals. The Smallmoney requires 4 bytes, while the equivalent decimal(10,4) needs at least 9 bytes. The money takes 8 bytes, while the decimal(19,4) needs 9 bytes.

As you can see use of Smallmoney significantly saves lot of space.

Creating Table with Money Data type

Inserting & Retrieving Values

References

money and smallmoney (Transact-SQL)

Leave a Comment

Your email address will not be published.

Scroll to Top