Here’s a Dynamics riddle for you: When is a field not a field? Answer: when it is 2 or more fields.

Way back in tip 273, we discussed the maximum number of fields you can have in a Dynamics 365 entity. That maximum is for text/string fields. You should be aware that certain types of fields will consume more than one field in the database, reducing the limit on total fields. (To clarify: 1,024 columns per table is one of the limits imposed by SQL Server).

3 thoughts on “Tip #933: When a field is not a field”

In the rush to the cloud we can sometimes overlook that an Online org only ever exposes the filtered views.

Because the filtered views always return and ID and name for a lookup field we can get the impression that each lookup consumes two columns in SQL. However, in SQL a lookup field consumes a single ID column.

Likewise with Option Sets. The filtered views return them as value and text, but in SQL, they are a single integer column.

Here is an alphabetized query of the AccountBase table from a CRM 2016 org. As you can see, things like AccountCategoryCode and PrimaryContactId are single columns:

@Aron Fishman,
you have the limits because the Filtered Views cannot have more than 1024 columns. (standard fields + the “virtual fields” for the complex types.

Online instances do not expose the Filtered Views, you can do a SQL query only for OnPremise instances, and “Filtered Views” mechanism is the only supported way to read data from CRM using SQL (due to the security context and the MS guarantee that they will work in that way)