Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Maximum Record Length

Jump to Best Answer
  • 1.  Maximum Record Length

    SILVER CONTRIBUTOR
    Posted Apr 29, 2020 05:37 AM
    Hi experts

    Maybe a strange question...
    What is the maximum record length of an entity in CRM 2016 (On prem, running on SQL Server 2016)?
    Reason for this question: Is there a need to skimp with the lenght of new textfields?
    Thank you in advance.
    Heinz
    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: Maximum Record Length
    Best Answer

    TOP CONTRIBUTOR
    Posted Apr 30, 2020 08:40 AM
    CRM Database Limits:

    • CRM stores all records in SQL tables, the limits imposed are mostly the same as SQL limits
    • The default fields are stored in one table, while the custom fields are stored in another - so your custom fields have use of a full SQL table
    • 1024 columns (note that different CRM field types use a different number of columns - so the real number of fields is probably somewhat closer to 600-800.
    • I'm not certain, but I believe there is also a limit of 8060 bytes per record (this may be a hard limit or a best practices recommendation for performance - and it may no longer be current - so I would regard this as a soft limit - you're safe below this limit, but if you think you may need to go over, then do some testing to be sure) 
    • In general, multiply the total length of your text field by 2 in order to get the number of bytes.
    • I believe SQL only consumes the actual text length populated in text fields - so you may not see a performance hit until you have records that have these fields fully populated
    And I believe text fields are limited to 4000 characters - so with one field of that size you may use nearly all of the recommended record size.

    All of that is directional only - but hopefully helpful.

    Check back in with us if you do some testing or learn something else.

    ------------------------------
    Geoff Ables
    Managing Partner
    C5 Insight
    Charlotte NC
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: Maximum Record Length

    SILVER CONTRIBUTOR
    Posted Apr 30, 2020 10:57 AM
    Hi Geoff
    Thanks a lot for your detailed answer.
    I just had a view to the account table (dbo.AccountBase) in SQL Server Management Studio.
    Is there an easy possibility to see the "width" of the table definition, for example in properties?
    And last but not least, after reading your answer concerning custom fields I'm not fully sure dbo.AccountBase is the right one? It contains IMO standard and custom fields.
    Thank you in advance, very appreciated.
    Regards
    Heinz
    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 4.  RE: Maximum Record Length

    TOP CONTRIBUTOR
    Posted May 01, 2020 07:55 AM
    Hi @Heinz Arnold,

    I'm not a SQL expert, so you're stretching my knowledge a bit here. But here are a few thoughts:

    • Be careful about directly accessing the SQL tables for CRM. I've worked with many clients who have made some direct changes to the database and it has messed up their deployment so much that they were unable to successfully apply an upgrade and/or their migration to Online cost more than it needed to by several orders of magnitude. Always use the API. If developing reports, use the FilteredViews rather than direct entity access - and preferably use FetchXML so your reports can be portable to Online.
    • You may want to try either the XRMToolBox (https://www.xrmtoolbox.com/) or the Meta Data Explorer (https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/developer/browse-your-metadata) for access to some advanced reporting and analytics on your database.
    • I believe I was incorrect in my earlier email saying that the tables are split (the split was done many versions ago to create more attributes, but I believe the tables were re-joined into a single table a few more versions later because SQL limitations had changed) - so all of your attributes probably are in the accountbase table (to be sure this is correct, make sure you don't see another table with a name somewhat like accountbaseextended).​


    ------------------------------
    Geoff Ables
    Managing Partner
    C5 Insight
    Charlotte NC
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 5.  RE: Maximum Record Length

    SILVER CONTRIBUTOR
    Posted May 01, 2020 08:44 AM
    Hi Geoff
    Perfect! Thank you for your advice and further tips. Very welcome indeed. I'll give the toolbox a try and will post here, if I find something worth sharing.
    Thanks again, and happy weekend
    Heinz
    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


If you've found this thread useful, dive deeper into User Group community content by role