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.

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

I hope it helps.