Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Best approach

Author  Topic 

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-16 : 02:58:48
hello everybody,
which is the best datatype for storing money in the database.
'decimal' or 'money'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-16 : 07:59:35
When I was gainfully employed, I used to work in the financial industry. I have always used decimal - mainly because I can control the precision exactly. To be sure, I have not investigated money and smallmoney in any detail; they may have some advantages; I will defer to anyone who has good/bad experience with money.

If you are working with small amounts and using just one currency, it may not matter which one you use. But if the amounts involved are large, and if you need FX conversions, I would prefer decimal.
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-16 : 08:02:46
quote:
Originally posted by sunitabeck

When I was gainfully employed, I used to work in the financial industry. I have always used decimal - mainly because I can control the precision exactly. To be sure, I have not investigated money and smallmoney in any detail; they may have some advantages; I will defer to anyone who has good/bad experience with money.

If you are working with small amounts and using just one currency, it may not matter which one you use. But if the amounts involved are large, and if you need FX conversions, I would prefer decimal.


Thank you very much for sharing your experience
Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-16 : 17:20:33
DECIMAL.

1) It is proprietary, so porting it is a pain. It is one of the many
"Sybase Code Museum" features from decades ago. Remember the early
versions of UNIX?

2) Writing code in dialect when you don't need to make you sound like
a hillbilly to people that speak the language. You are better off with
DECIMAL(s,p) so you can use a properly sized column.

3) It does display and formatting in the back end, with commas and
dollar signs. That defeats the purpose of a tiered architecture.

4) The MONEY data type has rounding errors.

Using more than one operation (multiplication or division) on money
columns will produce severe rounding errors. A simple way to visualize
money arithmetic is to place a ROUND() function calls after every
operation. For example,

Amount = (Portion / total_amt) * gross_amt

can be rewritten using money arithmetic as:

Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)

Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.
BEGIN
DECLARE @gross_amt MONEY,
@total_amt MONEY,
@my_part MONEY,
@money_result MONEY,
@float_result FLOAT,
@all_floats FLOAT;

SET @gross_amt = 55294.72;
SET @total_amt = 7328.75;
SET @my_part = 1793.33;

SET @money_result = (@my_part / @total_amt) * @gross_amt;
SET @float_result = (@my_part / @total_amt) * @gross_amt;
SET @Retult3 = (CAST(@my_part AS FLOAT)
/ CAST( @total_amt AS FLOAT))
* CAST(FLOAT, @gross_amtAS FLOAT);

SELECT @money_result, @float_result, @all_floats;
END;

@money_result = 13525.09 -- incorrect
@float_result = 13525.0885 -- incorrect
@all_floats = 13530.5038673171 -- correct, with a -5.42 error


--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-17 : 01:10:02
quote:
Originally posted by jcelko

DECIMAL.

1) It is proprietary, so porting it is a pain. It is one of the many
"Sybase Code Museum" features from decades ago. Remember the early
versions of UNIX?

2) Writing code in dialect when you don't need to make you sound like
a hillbilly to people that speak the language. You are better off with
DECIMAL(s,p) so you can use a properly sized column.
Thank you very much for providing such information.

3) It does display and formatting in the back end, with commas and
dollar signs. That defeats the purpose of a tiered architecture.

4) The MONEY data type has rounding errors.

Using more than one operation (multiplication or division) on money
columns will produce severe rounding errors. A simple way to visualize
money arithmetic is to place a ROUND() function calls after every
operation. For example,

Amount = (Portion / total_amt) * gross_amt

can be rewritten using money arithmetic as:

Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)

Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.
BEGIN
DECLARE @gross_amt MONEY,
@total_amt MONEY,
@my_part MONEY,
@money_result MONEY,
@float_result FLOAT,
@all_floats FLOAT;

SET @gross_amt = 55294.72;
SET @total_amt = 7328.75;
SET @my_part = 1793.33;

SET @money_result = (@my_part / @total_amt) * @gross_amt;
SET @float_result = (@my_part / @total_amt) * @gross_amt;
SET @Retult3 = (CAST(@my_part AS FLOAT)
/ CAST( @total_amt AS FLOAT))
* CAST(FLOAT, @gross_amtAS FLOAT);

SELECT @money_result, @float_result, @all_floats;
END;

@money_result = 13525.09 -- incorrect
@float_result = 13525.0885 -- incorrect
@all_floats = 13530.5038673171 -- correct, with a -5.42 error


--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL


Go to Top of Page
   

- Advertisement -