August 7, 2015 at 2:36 pm
For purposes of storing prices, total dollars, etc. and then computing grand total dollars via SUM, isn't best just to go with Money as the column data type ?
If small money was used, everytime you would sum it, you would have to cast it to money to prevent overflow, right ?
Also, isn't Float a horrible type to use for financial data ?
August 7, 2015 at 3:09 pm
mar.ko (8/7/2015)
For purposes of storing prices, total dollars, etc. and then computing grand total dollars via SUM, isn't best just to go with Money as the column data type ?If small money was used, everytime you would sum it, you would have to cast it to money to prevent overflow, right ?
Also, isn't Float a horrible type to use for financial data ?
Yes DO NOT use float for financial data. Float is an approximate datatype. when it comes to people's money you want to be precise and exact!!! Personally I would use a numeric with the appropriate scale and precision for what you are storing. Unless you are calculating interest where portions of a cent are needed then the money datatype has more precision than you really need because it goes to 4 insignificant digits. I would think something like numeric(9,2) would be sufficient but the scale may or may not be enough depending on what you are storing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2015 at 11:20 am
Sean Lange (8/7/2015)
mar.ko (8/7/2015)
For purposes of storing prices, total dollars, etc. and then computing grand total dollars via SUM, isn't best just to go with Money as the column data type ?If small money was used, everytime you would sum it, you would have to cast it to money to prevent overflow, right ?
Also, isn't Float a horrible type to use for financial data ?
Yes DO NOT use float for financial data. Float is an approximate datatype. when it comes to people's money you want to be precise and exact!!! Personally I would use a numeric with the appropriate scale and precision for what you are storing. Unless you are calculating interest where portions of a cent are needed then the money datatype has more precision than you really need because it goes to 4 insignificant digits. I would think something like numeric(9,2) would be sufficient but the scale may or may not be enough depending on what you are storing.
I think Sean is right on here. Using numeric allows you to control the precision. Just make sure you allow enough places to hold your largest number you're going to need to store. Don't use float for dealing with financial data.
August 10, 2015 at 7:02 am
Thanks Guys. I think decimal(9,2) is adequate....and it uses only 5 bytes.
The only problem :
when summing large numbers of rows....that could result in overflow if it is not re-cast, correct ?
August 10, 2015 at 11:45 am
mar.ko (8/10/2015)
Thanks Guys. I think decimal(9,2) is adequate....and it uses only 5 bytes.The only problem :
when summing large numbers of rows....that could result in overflow if it is not re-cast, correct ?
I'm not 100% sure, but I think that the aggregate functions generate a data type for the result. I have seen a VIEW that used UNION ALL taking a decimal(12,4) field and using SUM on it, where the data type for the same column in the other half of the UNION ALL was integer, and the resulting datatype was decimal(38,4), so I don't know that you'd necessarily overflow, but you could get growth in the data type. As I was working with a server replacement, and a change in datatype from float to decimal(12,4), the new view was now a decimal(38,4) datatype instead of a float, so the MS Access linked table had to be both refreshed as well as having a number of queries adjusted, because the new data type came across to Access as text over the ODBC connection, instead of a number.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 10, 2015 at 12:27 pm
sgmunson (8/10/2015)
mar.ko (8/10/2015)
Thanks Guys. I think decimal(9,2) is adequate....and it uses only 5 bytes.The only problem :
when summing large numbers of rows....that could result in overflow if it is not re-cast, correct ?
I'm not 100% sure, but I think that the aggregate functions generate a data type for the result. I have seen a VIEW that used UNION ALL taking a decimal(12,4) field and using SUM on it, where the data type for the same column in the other half of the UNION ALL was integer, and the resulting datatype was decimal(38,4), so I don't know that you'd necessarily overflow, but you could get growth in the data type. As I was working with a server replacement, and a change in datatype from float to decimal(12,4), the new view was now a decimal(38,4) datatype instead of a float, so the MS Access linked table had to be both refreshed as well as having a number of queries adjusted, because the new data type came across to Access as text over the ODBC connection, instead of a number.
The documentation is very clear on the return datatype.
https://msdn.microsoft.com/en-us/library/ms187810.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2015 at 12:28 pm
mar.ko (8/10/2015)
Thanks Guys. I think decimal(9,2) is adequate....and it uses only 5 bytes.The only problem :
when summing large numbers of rows....that could result in overflow if it is not re-cast, correct ?
Unless you have an unbelievably high number of rows you are NOT to overflow because SUM of a decimal(9,2) will return the results as a decimal(38,2).
https://msdn.microsoft.com/en-us/library/ms187810.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2015 at 1:59 pm
Sean Lange (8/10/2015)
sgmunson (8/10/2015)
mar.ko (8/10/2015)
Thanks Guys. I think decimal(9,2) is adequate....and it uses only 5 bytes.The only problem :
when summing large numbers of rows....that could result in overflow if it is not re-cast, correct ?
I'm not 100% sure, but I think that the aggregate functions generate a data type for the result. I have seen a VIEW that used UNION ALL taking a decimal(12,4) field and using SUM on it, where the data type for the same column in the other half of the UNION ALL was integer, and the resulting datatype was decimal(38,4), so I don't know that you'd necessarily overflow, but you could get growth in the data type. As I was working with a server replacement, and a change in datatype from float to decimal(12,4), the new view was now a decimal(38,4) datatype instead of a float, so the MS Access linked table had to be both refreshed as well as having a number of queries adjusted, because the new data type came across to Access as text over the ODBC connection, instead of a number.
The documentation is very clear on the return datatype.
Sean,
Thanks for the link. I was pretty sure that the SUM aggregate can bump up the data type as needed, and the doc clearly bumps up all the smaller integer types to int, and leaves int and bigint alone, while upping smallmoney to money, decimal(p, s) to decimal(38, s), and changing real to float, with money and float staying put. I hadn't actually looked at the doc for SUM() before, as I've never needed to worry about the data type coming out of it before. Had I not had the view and MS Access to deal with, I might well never have noticed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 11, 2015 at 5:46 am
If you're going to use the decimal data type, you should probably go to 4 decimal places. There is rounding that needs to take place behind the scenes. I have a vague memory this is some kind of standard, but can't swear to that anymore without some research.
August 11, 2015 at 6:55 am
RonKyle (8/11/2015)
If you're going to use the decimal data type, you should probably go to 4 decimal places. There is rounding that needs to take place behind the scenes. I have a vague memory this is some kind of standard, but can't swear to that anymore without some research.
When you have to perform division it will round which is why often times financial applications go to 4 decimal places. This often happens when calculating interest.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2015 at 7:10 am
When you have to perform division it will round
Dividing isn't limited to interest though. Calculating averages will also do this. I think I'll stick with four decimals as that's a common function.
August 11, 2015 at 7:18 am
Great discussion guys.....that comment about the rounding upon division was something I had not considered.
It sounds like division on DECIMAL(9,2) would produce a result that could be "off" by 1 cent or less.
August 11, 2015 at 7:20 am
RonKyle (8/11/2015)
When you have to perform division it will round
Dividing isn't limited to interest though. Calculating averages will also do this. I think I'll stick with four decimals as that's a common function.
Of course it isn't limited to calculating interest. That was an example. It all depends on what you are doing. If you are tracking sales 4 decimal places is overkill. You are not going to be dividing the sales totals when creating an invoice. It all comes down to usage. There are times when you need 4 decimal places for money and other times it just isn't needed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2015 at 7:30 am
You are not going to be dividing the sales totals when creating an invoice
But average sales per invoice would be normal. It may be because I work almost exclusively in an OLAP environment rather than an OLTP environment. I didn't think you were trying to be comprehensive, but it seemed that there was a more basic example than calculating interest.
August 11, 2015 at 8:21 am
FLOAT and REAL are also illegal under EU and GAAP rules because they have rounding errors.
What's your recommendation then?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply