Tip! How to Select a CRM Numeric Field Data Type in Microsoft Dynamics CRM

Posted By
BDO

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

Additional Notes

– 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
Whole Number int 4 0 -2,147,483,648 2,147,483,648
Floating Point Number float 8 5 -100,000,000,000 100,000,000,000
Decimal Number decimal 13 10 -100,000,000,000 100,000,000,000
Currency money 8 4 -992,337,203,685,477 992,337,203,685,477

BDO eBook - The Cloud Changes the Game

There are times in the course of your business when you have the opportunity to dramatically accelerate growth and improve day-to-day efficiencies. Recognizing

Download