Field Limits in Dynamics 365: How many fields is too many fields?

Do you plan on creating an entity with hundreds of fields?  Were you told that you will hurt user-adoption and ease of use?  Are you planning on doing it anyway? Great! But be cautious, because there are different limits on the amount of fields that can be added to an entity in Dynamics 365.  There is currently no way of seeing these limits in Dynamics 365 before you reach them. Once you reach the field limit, you may begin seeing SQL errors that can be difficult to resolve, especially in a Production environment.

Field limit on SQL tables

Let's talk about the different SQL limits related to an excess of fields.  There are two main limitations to pay attention to: the maximum number of fields that can exist on an entity, and the total amount of data that can be stored within those fields.  Newer versions of SQL have extended these limits, so it's best to discuss this with your system administrator first.  For this blog, we will discuss limitations on SQL Server 2008.

First, an entity's set of fields are stored in a SQL table with a maximum of 1024 columns.

Each field takes up 1, 2 or 3 columns depending on it's type.

• Lookup fields take up 3 columns.
• Option sets, boolean, and money fields take up 2 columns.
• All other field types take up 1 column.

Using this information, you can calculate the maximum number of fields before you will start experiencing SQL errors related to this limit.

Second, data contained for all fields within an entity are contained in a SQL table with a maximum storage of 8060 bytes.

Here is where things can get tricky.  Depending on the field type and that field type's settings, the amount of storage each field consumes can vary wildly.  Here is a breakdown of each field type and how many bytes they take up on a SQL server:

• Optionset: 4 bytes
• WholeNumber: 4 Bytes
• FloatingPointNumber: precision 0-25 = 4 bytes. precision 25 or more = 8 bytes
• Decimal Number: Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes
• Currency: 8 bytes
• Date/Time: 8 bytes
• Boolean: 0.125 bytes (1 bit)
• Lookup/Unique Identifier: 16 bytes*
• String/Memo/Text: Length x 2 = Bytes

*Lookup fields are tricky.  The lookup field's value is 16 bytes for it's Unique Identifier .  However, the Lookup field creates two additional fields that you cannot see in D365, but you can see them using the Entity Metadata Document Generator from XRMToolbox.  Two additional string fields are created behind the scenes, each with a length of 100.  So, in total, Lookup fields are worth 416 Bytes each(!), 200 per string, and 16 for the Unique Identifier.

If your planned set of fields exceeds one or both of these limits, consider moving some fields into related entities.

Beringer Technology Group, a leading Microsoft Gold Certified Partner specializing in Microsoft Dynamics 365 and CRM for Distribution. We also provide expert Managed IT Services, Backup and Disaster Recovery, Cloud Based Computing and Unified Communication Solutions.

The post Field Limits in Dynamics 365: How many fields is too many fields? appeared first on CRM Software Blog | Dynamics 365.