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.
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. |
|
|
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 |
|
|
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 earlyversions of UNIX?2) Writing code in dialect when you don't need to make you sound likea hillbilly to people that speak the language. You are better off withDECIMAL(s,p) so you can use a properly sized column.3) It does display and formatting in the back end, with commas anddollar 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 moneycolumns will produce severe rounding errors. A simple way to visualizemoney arithmetic is to place a ROUND() function calls after everyoperation. For example,Amount = (Portion / total_amt) * gross_amtcan 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 thenumbers you are using are greater than 10,000.BEGINDECLARE @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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
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 earlyversions of UNIX?2) Writing code in dialect when you don't need to make you sound likea hillbilly to people that speak the language. You are better off withDECIMAL(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 anddollar 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 moneycolumns will produce severe rounding errors. A simple way to visualizemoney arithmetic is to place a ROUND() function calls after everyoperation. For example,Amount = (Portion / total_amt) * gross_amtcan 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 thenumbers you are using are greater than 10,000.BEGINDECLARE @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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
|
|
|
|
|
|
|
|