Best Practices - Maximum number of fields on an entity?

(0) Share

Report

Posted on by RMH

265

I'm thinking through some design issues for an update to an existing CRM 2011 on-premises installation. One approach would be to add a bunch of fields to an existing entity (currently about 200 fields on the entity). The new fields would bring the total up to 250 fields on the existing entity.

From my SQL background, I look at 250 columns in a table and shudder. Is this many fields common on a custom entity in CRM 2011 (I'm a bit new to CRM)? Or is this many pretty unusual?

Any thoughts or experience to share on this?

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (2)

Community Member on at

Like (0)

Report

RE: Best Practices - Maximum number of fields on an entity?

The Filtered views provide a lot of additional info about certain fields, depending on the field type.

For example adding a lookup field to an entity results in 3 columns being added to the SQL Filtered view, an Option Set results in 2 fields. Look at the Verified Answer from Churchill Kumar for more details.

Not all of these additional columns are added to the non filtered view, so the Filtered view will always have more columns than the base table or non filtered view.

Therefore you only need to worry about Filtered views that are approaching the limit of 1024 columns.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Best Practices - Maximum number of fields on an entity?

Hi Andy,

Great query thanks.

We use Dynamics CRM 2013. I don't do any development work myself on the system as we have a contracted supplier however I do have access to the test database so I can run queries and I would like to understand better how it works.

As such I would like to ask why it is only the Filtered views that you are picking in your query.

For instance I know that our cases are stored in "Incident" table, and when I commented out the "Filtered" from your query and ran it, I got the following (extract):

Name Fieldcount

FilteredIncident 995

Incident 643

Why is the number of fields different and why only focus on "Filtered"?

Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report
RE: Best Practices - Maximum number of fields on an entity?

If you have access to the database you could also use this query to identify any entities likely to cause problems soon.

select v.name , count(c.name) as fieldcount from sys.views v inner join sys.columns c on v.object_id = c.object_id where v.name like 'filtered%' group by v.name order by 2 desc

Was this reply helpful? Yes No
Joe Woltering 12,163 on at

Like (0)

Report

RE: Best Practices - Maximum number of fields on an entity?

What about when option sets are involved? I have a form with a large number of option set fields (~50) and this form seems to take longer to load than other and that's the only factor that I can attribute to its causing the performance lag.

Was this reply helpful? Yes No
Deepak N 70 on at

Like (0)

Report

RE: Best Practices - Maximum number of fields on an entity?

Hi,

I've reached the maximum limit, in creating fields on a particular Entity. But still I need to add some more fields to my Form. Can u suggest me with a solution "to add more Fields to same Entity"?

Was this reply helpful? Yes No
RMH 265 on at

Like (0)

Report

RE: Best Practices - Maximum number of fields on an entity?

Thanks, Churchill! I've decided to create new entities rather than pile more columns on an existing entity. Now I have to start wading through the fetchXML...

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (3)

Report

RE: Best Practices - Maximum number of fields on an entity?

Hello RMH,

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.

Thank You.

Was this reply helpful? Yes No
Suggested answer

Ryan Maclean 3,070 on at

Like (0)

Report

RE: Best Practices - Maximum number of fields on an entity?

Hi RMH,

before adding any fields it may be worth reviewing which of the existing fields are already being used. I'm also relatively new to CRM, but when I inherited the system in use at my workplace from my predecessor i carried out an audit of the system to see what was still in use and what was redundant so I could declutter the system. I found there were a large amount of fields that were not in use at all, or were inconsistent in their use across records within an entity.

You could use a solution like CRM Data Detective (crmdatadetective.codeplex.com) to give you an overview of field usage and this might allow you to trim the amount of fields that you have in place.

Ryan

Was this reply helpful? Yes No
Suggested answer

SyedHussain 1,820 on at

Like (0)

Report

RE: Best Practices - Maximum number of fields on an entity?

1024 is the maximum number of columns you can have on any form. CRM isn't the limitation but SQL server (msdn.microsoft.com/.../ms143432.aspx).

The columns will lay dormant until you either retrieve them using code or the CRM forms. There is always a performance hit, but the extent is dependant on the hardware the CRM is running under. You also have the auto-save to contend with in CRM 2013. My suggestion to you is to think about your design well because it's more problematic to revert.

Was this reply helpful? Yes No
Verified answer

Hosk on at

Like (0)

Report

RE: Best Practices - Maximum number of fields on an entity?

This is quite a few fields but I have seen entities with more.

In most cases crm SQL is fairly simple and the amount of fields don't seem to effect performance much.

Also usually not all fields are usually used

In the code you only retrieve the girls you need.

I would worry about the fields but logically go through the design to see if adding the fields to another entity would make sense from a design perspective

Was this reply helpful? Yes No