Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
Field types: varchar, char, decimal |
Wed, Apr 27 2016 9:07 AM | Permanent Link |
kamran | Hi
Just getting to know edb. Loving it so far. I needed some clarification of using field types correctly. 1. VarChar or Char They are both the same ? right ? Not sure what the diffrences would be in usage/ sql/ searching etc. Is there any merits for using one over the other.? 2. Decimal with a scale of 2 Is that sufficient / best practice to use for day to day money calculations and to *stop* any rounding errors further down the line? 3. Using GUID Is that automatically maintained once defined in table structure ? If not, do I have to generate this, Is there a prebuilt function for this?. Kind regards Kamran |
Wed, Apr 27 2016 9:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | kamran
>I needed some clarification of using field types correctly. > >1. VarChar or Char > >They are both the same ? right ? No - Char is right padded with spaces VarChar isn't, but it will store any spaces appended by the user >Not sure what the differences would be in usage/ sql/ searching etc. Table size - no, index size - not sure, searching speed - unnoticeable >Is there any merits for using one over the other.? I don't think there's much in it these days. Unless I have a single character column I always use VARCHAR. At one point the trailing spaces were significant in terms of sql filter conditions but (I think) Tim altered that. It can still be a problem in Delphi where the trailing spaces will prevent a math. >2. Decimal with a scale of 2 > >Is that sufficient / best practice to use for day to day money calculations and to *stop* >any rounding errors further down the line? Should do >3. Using GUID > >Is that automatically maintained once defined in table structure ? If you set the column to GUID and generated and use CURRENT_GUID as the generation expression it will Roy Lambert |
Wed, Apr 27 2016 11:14 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << Not sure what the diffrences would be in usage/ sql/ searching etc. >> Don't use CHAR unless you understand the differences in how it works. For 99.9% of the cases out there, VARCHAR is what you want. << Is that sufficient / best practice to use for day to day money calculations and to *stop* any rounding errors further down the line? >> Yes. << 3. Using GUID Is that automatically maintained once defined in table structure ? >> What do you mean by "automatically-maintained" ? Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 27 2016 12:16 PM | Permanent Link |
Adam Brett Orixa Systems | Kamran
>>2. Decimal with a scale of 2 >> >>Is that sufficient / best practice to use for day to day money calculations and to *stop* >>any rounding errors further down the line? >Should do Only thing I would add to Roy's points are: 1. I have some customers who like to record the cost of products and ingredients to a currency accuracy of more than 2 decimal places ... 2. You should be careful of how different systems manage rounding. In rare case some systems round down and others round up in situations such as 1.005 becoming either 1.01 or 1.00. For example UK tax office wages has different norms to "normal" maths ... so you need to cope with this. For the above reasons I tend to stick with 4 decimal places for all "Currency" fields (defined DECIMAL(19,4) ) just for safety. |
Thu, Apr 28 2016 4:02 AM | Permanent Link |
Matthew Jones | Adam Brett wrote:
> 1. I have some customers who like to record the cost of products and > ingredients to a currency accuracy of more than 2 decimal places ... The system I am working on for a client uses 5 deciman places, because at scale you can end up "losing" thousands of pounds. If you buy 10,000 of something at 1.02 instead of 1.015, that's real money and can mean winning or losing business or profit. So this is something to consider, even if you reject it and stick with 2 decimal places. -- Matthew Jones |
Thu, Apr 28 2016 7:01 AM | Permanent Link |
kamran | Hi
Thank you to everyone. I get it now. Regards Kamran |
Fri, Apr 29 2016 12:43 PM | Permanent Link |
Fernando Dias Team Elevate | Hey
Almost everything has been already said, I would only like to add 2 details about the DECIMAL type: 1. Regardless of the precision you specify, EDB always uses an implementation-defined precision of 19 digits with a maximum of 4 decimal places. That means that if you need 5 decimal places, as Matthew illustrated, you would have to use the DOUBLE PRECISION or FLOAT type instead, but thats not an exact type but a floating point type that can cause rounding and representation errors. 2. When the values are money amounts, I always use DECIMAL(19,2) for totals (ie, Total value of an invoice) but DECIMAL(19, 4) for other values that are not totals, like unit prices of items for example. The reason is the same that Matthew has already referred. -- Fernando Dias [Team Elevate] |
Fri, Apr 29 2016 1:16 PM | Permanent Link |
kamran | Hi Fernando
Thanks for clarifying further. Cheers Kamran Fernando Dias wrote: Hey Almost everything has been already said, I would only like to add 2 details about the DECIMAL type: 1. Regardless of the precision you specify, EDB always uses an implementation-defined precision of 19 digits with a maximum of 4 decimal places. That means that if you need 5 decimal places, as Matthew illustrated, you would have to use the DOUBLE PRECISION or FLOAT type instead, but thats not an exact type but a floating point type that can cause rounding and representation errors. 2. When the values are money amounts, I always use DECIMAL(19,2) for totals (ie, Total value of an invoice) but DECIMAL(19, 4) for other values that are not totals, like unit prices of items for example. The reason is the same that Matthew has already referred. -- Fernando Dias [Team Elevate] |
This web page was last updated on Friday, May 3, 2024 at 08:07 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |