of the
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).
Adam Vero clarifies:
- Option sets and two options (bit fields) each use 2 columns, regardless of how many possible options.
- Lookups are also two – the GUID and name.
- Polymorphic lookups like customer and regarding may use an extra one for the target entity type.
- Currency fields will also be two each, plus 3 overall on the entity for the exchange rate and currency ID and name.
- Rollup fields of any kind, because these will add two additional fields for the current status of the rollup field and the date/time it was last calculated
Tipp Jaar: The rollup currency field where it’s the only currency field on the entity takes the cake with 8 fields being consumed in the process.
Hello Guys,
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:
SELECT [AccountId]
,[AccountCategoryCode]
,[AccountClassificationCode]
,[AccountNumber]
,[AccountRatingCode]
,[Aging30]
,[Aging30_Base]
,[Aging60]
,[Aging60_Base]
,[Aging90]
,[Aging90_Base]
,[BusinessTypeCode]
,[CreatedBy]
,[CreatedByExternalParty]
,[CreatedOn]
,[CreatedOnBehalfBy]
,[CreditLimit]
,[CreditLimit_Base]
,[CreditOnHold]
,[CustomerSizeCode]
,[CustomerTypeCode]
,[DefaultPriceLevelId]
,[Description]
,[DoNotBulkEMail]
,[DoNotBulkPostalMail]
,[DoNotEMail]
,[DoNotFax]
,[DoNotPhone]
,[DoNotPostalMail]
,[DoNotSendMM]
,[EMailAddress1]
,[EMailAddress2]
,[EMailAddress3]
,[EntityImageId]
,[ExchangeRate]
,[Fax]
,[FtpSiteURL]
,[ImportSequenceNumber]
,[IndustryCode]
,[IsPrivate]
,[LastOnHoldTime]
,[LastUsedInCampaign]
,[MarketCap]
,[MarketCap_Base]
,[MasterId]
,[Merged]
,[ModifiedBy]
,[ModifiedByExternalParty]
,[ModifiedOn]
,[ModifiedOnBehalfBy]
,[Name]
,[NumberOfEmployees]
,[OnHoldTime]
,[OpenDeals]
,[OpenDeals_Date]
,[OpenDeals_State]
,[OpenRevenue]
,[OpenRevenue_Base]
,[OpenRevenue_Date]
,[OpenRevenue_State]
,[OriginatingLeadId]
,[OverriddenCreatedOn]
,[OwnerId]
,[OwnerIdType]
,[OwnershipCode]
,[OwningBusinessUnit]
,[ParentAccountId]
,[ParticipatesInWorkflow]
,[PaymentTermsCode]
,[PreferredAppointmentDayCode]
,[PreferredAppointmentTimeCode]
,[PreferredContactMethodCode]
,[PreferredEquipmentId]
,[PreferredServiceId]
,[PreferredSystemUserId]
,[PrimaryContactId]
,[PrimarySatoriId]
,[PrimaryTwitterId]
,[ProcessId]
,[Revenue]
,[Revenue_Base]
,[SharesOutstanding]
,[ShippingMethodCode]
,[SIC]
,[SLAId]
,[SLAInvokedId]
,[StageId]
,[StateCode]
,[StatusCode]
,[StockExchange]
,[Telephone1]
,[Telephone2]
,[Telephone3]
,[TerritoryCode]
,[TerritoryId]
,[TickerSymbol]
,[TimeZoneRuleVersionNumber]
,[TransactionCurrencyId]
,[TraversedPath]
,[UTCConversionTimeZoneCode]
,[VersionNumber]
,[WebSiteURL]
,[YomiName]
FROM AccountBase
@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)
If in CRM 2011 I wrote a SQL query like
SELECT firstname, lastname, new_customfield FROM FilteredContact
it will still work with the latest Dynamics 365 OnPremise version
If someone wrote
SELECT firstname, lastname FROM ContactBase INNER JOIN ContactExtensionBase (…)
it already stopped to work in CRM 2015 because the extension tables do not exist anymore but the Filtered Views continued to work without issues.
Developer note: if the 1024 column limit is reached, in my opinion 90% of the cases is due to bad design
Thanks Guido for the clarification. It is good to know that the views also have a limit of 1024 columns.
And, I should have used better language to make the point that the API’s rely on the filtered views rather than stating that online orgs expose filtered views.
Thanks,
Aron
Wouldn’t a currency field be 4, if it was the first currently field? TransactionCurrencyId, ExchangeRate, [fieldname] and [fieldname]_base ?