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.4567**8**. 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 |

Rey HinckleyI 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?

Anonymousgood question, next question?