Customer Engagement & Dynamics CRM Forum

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

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
------------------------------

Original Message:
Sent: Apr 29, 2020 05:36 AM
From: Heinz Arnold
Subject: Maximum Record Length

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

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
Original Message:
Sent: Apr 30, 2020 08:40 AM
From: Geoff Ables
Subject: Maximum Record Length

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

Original Message:
Sent: Apr 29, 2020 05:36 AM
From: Heinz Arnold
Subject: Maximum Record Length

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

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
------------------------------

Original Message:
Sent: Apr 30, 2020 10:57 AM
From: Heinz Arnold
Subject: Maximum Record Length

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
Original Message:
Sent: Apr 30, 2020 08:40 AM
From: Geoff Ables
Subject: Maximum Record Length

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

Original Message:
Sent: Apr 29, 2020 05:36 AM
From: Heinz Arnold
Subject: Maximum Record Length

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

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
Original Message:
Sent: May 01, 2020 07:55 AM
From: Geoff Ables
Subject: Maximum Record Length

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

Original Message:
Sent: Apr 30, 2020 10:57 AM
From: Heinz Arnold
Subject: Maximum Record Length

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
Original Message:
Sent: Apr 30, 2020 08:40 AM
From: Geoff Ables
Subject: Maximum Record Length

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

Original Message:
Sent: Apr 29, 2020 05:36 AM
From: Heinz Arnold
Subject: Maximum Record Length

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