One of the many questions we encounter repeatedly is how to select the proper numeric field type in Microsoft Dynamics CRM. Determining the proper field type requires asking some questions about the data. Some are simpler than others:
1. Does the data represent money?
a. If yes, use the Currency field data type.
2. Does the data require decimals?
a. If no, then use the Whole Number data type.
If you have not selected a field type at this point, then the questions get a bit harder and there are more factors which need to be included in the decision. These factors include the number of fields in the Entity and the ultimate size of the SQL table, the accuracy required in the data set, and the number of decimals required.
Determining the size and limitations of the SQL table are beyond the scope of this document, so we will ignore these factors. What remains is the number of decimals and how you intend on using the data.
3. Do you require more than 5 decimal places?
a. If yes, then use the Decimal Number data type.
Other data usage factors to consider:
4. Are you going to be performing data sums across large sets of numbers or comparing the data using equal (=) or not equal (<>)?
a. If yes, then consider using the Decimal Number data type as they may perform faster.
5. Are you storing fractions or numeric values which will be compared using greater than (>) or less than (
a. If yes, then consider using the Floating Point Number data type as they may perform faster.
6. Are you dealing with a large number of decimal places and is the accuracy of the decimals important?
a.If yes, then consider using the Decimal Number data type. See Note below
– Decimal Numbers require more storage space, but they always calculate accurately to the number of specified decimals.
– Floating Point Numbers require less storage space and may calculate faster in some cases, but they may lose accuracy in the least significant decimal place.
If after reviewing all these factors, you are still undecided, my general rule of thumb is:
Use Floating Point Numbers, unless you require absolute accuracy in calculations involving a large number of decimals.
Summary of Numeric Field Characteristics
|CRM Numeric data types||Corresponding SQL Data Type||DB Storage (Bytes)||Max #of Decimals||Minimum Value||Maximum Value|
|Floating Point Number||float||8||5||-100,000,000,000||100,000,000,000|