Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
What is maximum alowable size of a record in CRM 4 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Could you please advice what is the maximum alowable size of a record in CRM 4?

    The reason I am asking this question is that our users asked us to increase the size for the comment field in one entity to 50000 which I know it is possible. But I would like to know if there is a limmitation in the size of the record it self in CRM.

    I am afraid by increasing the size of this field in my entity exceeded the maximum alowable length of the record.

    Do you have any ideas if there is any maximum?

    Any thoughts would be very much appricated.

     

    Tuesday, May 31, 2011 11:17 PM

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote

    Hi,

    A table can contain a maximum of 8,060 bytes per row. In SQL Server 2005/2008, this restrictioen is relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. The length of each one of these columns must still fall within the limit of 8,000 bytes; however, their combined widths can exceed the 8,060-byte limit. This applies to varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns when they are created and modified, and also to when data is updated or inserted.

    This restriction does not apply to varchar(max), nvarchar(max), varbinary(max), ntext, text, image, or xml columns. For more information about the storage of these columns, see Using Large-Value Data Types, Using text and image Data, and Using XML Data.

    There are performance issues related to this, i would recommend you to reffer:

    SQL 2005: http://msdn.microsoft.com/en-us/library/ms186981(v=SQL.90).aspx

    SQL 2008: http://msdn.microsoft.com/en-us/library/ms186981(v=SQL.100).aspx

    I hope this will answer your question.

    Thank You,
    Jehanzeb Javeed,
    http://worldofdynamics.blogspot.com
    Linked-In Profile |CodePlex Profile

    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer".

    • Proposed as answer by Jehanzeb.Javeed Thursday, June 2, 2011 10:33 PM
    • Marked as answer by Maryam_r Friday, June 3, 2011 11:39 PM
    Thursday, June 2, 2011 10:32 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi,

    In CRM 4 you can even have 100,000 characters length for ntext fields but you won't be able to search on this field, You have to make the field non-searchable or else you will start recieving errors because SQL Server has a limmit of Index size upto 900 byte maximum.

    There is no entity record maximum size limmit.

    I hope this will answer your questions.

    Thank You,
    Jehanzeb Javeed,
    http://worldofdynamics.blogspot.com
    Linked-In Profile |CodePlex Profile



    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer".
    • Proposed as answer by Jehanzeb.Javeed Thursday, June 2, 2011 8:20 PM
    • Marked as answer by Maryam_r Friday, June 3, 2011 11:40 PM
    Thursday, June 2, 2011 8:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Than you so much I have got my answer on the Ntext field.

    I have also 3 fields on the entity that are NVarchar and users would like to increase them to the maximum which is 4000.

    As you know then the length of each field would be 8000 in the database and I wonder if it causes me any issue.

    3 fields nvarchar fields * 8000 + around 1000 bytes for other fields on the table  + one ntext field  on one table > 8060.

    Do you know if this record size will cause me issue i mean beacuse of sql server?

     

     

    • Marked as answer by Maryam_r Friday, June 3, 2011 11:34 PM
    Thursday, June 2, 2011 10:07 PM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi,

    In CRM 4 you can even have 100,000 characters length for ntext fields but you won't be able to search on this field, You have to make the field non-searchable or else you will start recieving errors because SQL Server has a limmit of Index size upto 900 byte maximum.

    There is no entity record maximum size limmit.

    I hope this will answer your questions.

    Thank You,
    Jehanzeb Javeed,
    http://worldofdynamics.blogspot.com
    Linked-In Profile |CodePlex Profile



    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer".
    • Proposed as answer by Jehanzeb.Javeed Thursday, June 2, 2011 8:20 PM
    • Marked as answer by Maryam_r Friday, June 3, 2011 11:40 PM
    Thursday, June 2, 2011 8:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Than you so much I have got my answer on the Ntext field.

    I have also 3 fields on the entity that are NVarchar and users would like to increase them to the maximum which is 4000.

    As you know then the length of each field would be 8000 in the database and I wonder if it causes me any issue.

    3 fields nvarchar fields * 8000 + around 1000 bytes for other fields on the table  + one ntext field  on one table > 8060.

    Do you know if this record size will cause me issue i mean beacuse of sql server?

     

     

    • Marked as answer by Maryam_r Friday, June 3, 2011 11:34 PM
    Thursday, June 2, 2011 10:07 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    Hi,

    A table can contain a maximum of 8,060 bytes per row. In SQL Server 2005/2008, this restrictioen is relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. The length of each one of these columns must still fall within the limit of 8,000 bytes; however, their combined widths can exceed the 8,060-byte limit. This applies to varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns when they are created and modified, and also to when data is updated or inserted.

    This restriction does not apply to varchar(max), nvarchar(max), varbinary(max), ntext, text, image, or xml columns. For more information about the storage of these columns, see Using Large-Value Data Types, Using text and image Data, and Using XML Data.

    There are performance issues related to this, i would recommend you to reffer:

    SQL 2005: http://msdn.microsoft.com/en-us/library/ms186981(v=SQL.90).aspx

    SQL 2008: http://msdn.microsoft.com/en-us/library/ms186981(v=SQL.100).aspx

    I hope this will answer your question.

    Thank You,
    Jehanzeb Javeed,
    http://worldofdynamics.blogspot.com
    Linked-In Profile |CodePlex Profile

    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer".

    • Proposed as answer by Jehanzeb.Javeed Thursday, June 2, 2011 10:33 PM
    • Marked as answer by Maryam_r Friday, June 3, 2011 11:39 PM
    Thursday, June 2, 2011 10:32 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you so much for the very complete and percise answer which helped me a lot.

    I figured out nVarchar(max) can have 2 gig of data while nvarchar can accomodate 4000 characters which is 8k and if we use a combination of them if the row size is below 8K there would be no paging and much better performance.

    In my case that user would like to have 2 nvarchar fields each 4000 + 1 ntext(nvarchar(max)) field which is 50000 I beleive it will have a effect on the performance but as it is supported I guess it should be ok to use for one field.Hopefully it does not slow down the system significantly going forward.

    Thanks again,

    Friday, June 3, 2011 11:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    If your quesiton has been answered then please "Vote as Helpful" and "Mark As Answer".

    Thank You,
    Jehanzeb Javeed,
    http://worldofdynamics.blogspot.com
    Linked-In Profile |CodePlex Profile



    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer".
    Friday, June 3, 2011 11:36 PM