How many fields can you create in an Entity for Dynamics 365

Sometimes, you just need a lot of fields.

If you’ve ever wondered just how many is too many, look no further.

 

I recently had to create hundreds of fields for a single entity, but needed to be sure that I could actually fit all of the fields I required in this single entity. I found this discussion  on the Dynamics Community site that answered my question:

Churchill Kumar writes, “The SQL server tables limit is 1024 columns. This is the limit to the SQL Server views also.
When you create a new filed in a CRM entity the CRM add this field in the respective table and in the respective Filtered View. Depending on the field type the number of columns added in the Filtered view is different.

Example:

• If you add a text field in the Entity, the CRM will add 1 column in the Filtered View.
• If you add an Option Set field in the Entity, the CRM will add 2 columns in the Filtered View; 1 for the Value and 1 for the Label.
• If you add a Lookup field in the Entity, the CRM will add 3 columns in the Filtered View; 1 for the ID, 1 for the referenced Entity Logical Name, 1 for the Record Name.

So depending on the types of fields you have in the entity you can reach the 1024 limit at a different moment as the limit of the fields you can create in an Entity should be calculated as follows:

X *3 + Y*2 + Z*1 <= 1024

Where:

• X is the number of Lookup Fields
• Y is the number of Option Set / Boolean / Money fields
• Z is the number of fields from other type

Theoretically you can reach the limit at around 340 fields if you have a lot of Lookup fields in your entity: 340 *3 = 1020.”

In my case, I required over 500 lookup fields and 250 option set fields, so I ultimately had to split up the project into three entities, and it fundamentally changed how I was going to approach the rest of the project. So, if you’re ever in doubt when starting a new large project, it’s worth considering how many entities might be required to “fit” all of the fields you need.

 

 

 

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.