Field Limits in Dataverse: How many fields are too many fields?

Hi there!

I want to share with you the Secret de Polichinelle for Dataverse.

The other day I came across a seemingly obvious limitation, but in my personal practice, it appeared for the first time - a limitation on the number of fields created per entity. More specifically, the limitation on the number of fields that are used in the Filtered View.

What is a Filtered View? Filtered Views are SQL Server views that do wonderful things for the application developer needing to get access to data from the CRM system:

1. Filtered Views know about your CRM security permissions – which objects you are able to access and what you are allowed to do with them.
2. Filtered Views encapsulate all the standard table joins necessary to make sense of a CRM object: base tables to extension tables (for custom attributes), picklist values to strings, etc.
3. Filtered Views exist for all the user-visible CRM objects, including separate views for each activity type.
4. Filtered Views work for custom entities.

The problem I encountered was the inability to create more custom fields for a custom entity that only had 594 fields (ha-ha). And the funny thing is that I got this error:

Current number of filtered view attributes of 1001 will exceed the maximum limit of 1000 for entity

With the help of MS support, I did my investigation and identified the following points.

Resolution/Suggestion:

>>The issue you are facing is due to the By-Design behavior/limitation from the SQL side.

The maximum number of fields on an entity depends on the Maximum Capacity Specifications for SQL Server. The SQL server tables limit is 1024 columns. This is the limit to the SQL Server views also.

When we create a new field 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, and 1 for the Record Name.

So, depending on the type 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 types

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

This behavior is also explained in this blog here:

Field Limits in Dynamics 365: How many fields is too many fields? - CRM Software Blog | Dynamics 365

And regarding the difference in the number of fields showing up on UI and telemetry, this might be because some system fields are hidden and will not show up in the user interface and as you are aware the system fields can’t be deleted from the environment. So, you need to remove some fields by focusing on what you can see in the UI to be able to import the solution.

You can see all the fields of contact entity on environment by using this webAPI:

Org URl/api/data/v9.2//EntityDefinitions(Guid ID)/Attributes?$select=MetadataId,SchemaName

And one more "great" thing: okay, we did a review of the existing fields and decided to remove a few dozen, deleting them but leaving a couple of new fields.

That is, in other words, we had 594 fields, we wanted to add 3, but remove 50.

Seemingly super, but there is one "BUT". When working with managed solutions, import and upgrade solutions first add fields to the entity and then do the deletion!

We ended up having to do two upgrades! One is designed specifically for deleting fields, and one is for adding fields 🤪🤪🤪

So be careful. The way I see it, it's better to make many-many small entities than to make one giant one with hundreds of fields. 

But that's a completely different story...