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
Table of Contents
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 Type | Range from | Range to | Memory |
|---|---|---|---|
| smallmoney | −214,478.3648 | +214478.3647 | 4 bytes |
| money | −922,337, 203, 685,477.5808 | +922,337, 203, 685,477.5807 | 8 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.
1 2 3 4 5 6 7 8 9 | declare @num1 smallmoney set @num1 = 1234.5678 select (@num1 /10)* 10 ***Result 1234.567 |
While decimal(10,4) retains the precision.
1 2 3 4 5 6 7 8 | declare @num1 decimal(10,4) set @num1 = 1234.5678 select (@num1 /10)* 10 *****Result 1234.5678000 |
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
1 2 3 4 5 6 7 | Create Table TestMoney ( col1 smallmoney, col2 money ) |
Inserting & Retrieving Values
1 2 3 | insert into TestMoney (col1, col2) values(5000.50,100000.75) |
1 2 3 4 5 6 7 | select * from TestMoney **Result 5000.50 100000.75 |
I am at a loss. I have been trying to figure out if I can save money to sql data types.
I have downloaded from my bank all of my transactions. I get it in a .csv format.
I have used been using FoxPro to parse the information.
a few of the fields are $1,000.05 type format. Does SQL edit the field without me having to strip out the non numeric characters?
good question, next question?