Dynamics 365: How many fields can you create in an Entity?

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.