MS CRM Row Size Limit on Customizations

Oh grief!

My first major disappointment with MS CRM was the "web services" which prove to be soap over http. Today we started customizing the entities, which was another seemingly great feature of MS CRM. The concept of using a Schema Manager to maintain custom fields in the datastore is very nice but not when customization is extremely limited. A quick look to the newsgroups sums it up, Dave writes:

Actually I think there is a limit on the number as well as the combined size
of the columns:  1024 columns, not to exceed 8060 bytes.

The number of columns is not a real limit but, as you point out, the
combined size is.  Here are some stats on some of the common tables:

Account: 63 columns with a combined size of 2617K
Contact: 80 columns with a combined size of 6996K
Contract: 55 columns with a combined size of 4994K

This doesn't leave a lot of room for customization.

Well that can be said again. This is a lot of fun now that the limit is reached and the customer still has about 10 more must-have fields. Do note that I am actually Certified in MS CRM Customization, and had no clue about this limitation. Bad MBS!

UPDATE: So, when the frustration had risen from a dense mist to more of a dark cloud in the sky, the search for workarounds starts.

Option 1: Use "More Addresses"
Because entities like Contact and Account actually can have unlimitied addresses, these can be used as stores for data. Each address has 11 columns of type nvarchar(50). Additionally there is one nvarchar(150) and a bunch of misc. bit and id columns. Why use these? Well, they're there, and they are directly related in a 1-1 fashion with the enity. Why does it suck? Because you have to manually remember that the column [telephone1] in the address named [SpecialStuff] of the Contact entity actually contains this persons ICQ number. Also, this will make it virtually impossible to maintain the data within the MS CRM user interface because the forms customization cannot create special labels for your [SpecialStuff].

Option 2: Use a proprietary data store
Don't go there. The next version of MS CRM is said to maybe include better support for customizations.

Option 3: Use Memo fields
Memo fields map to ntext columns and can be used to store your SpecialStuff. A custom Memo field could be populated with XML based on a simple schema and the XML blob can be serialized and deserialized by your custom solutions. If you want to work with the data from the MS CRM user interface you'd at least be able to edit the XML with a bit more accuracy. Of course, all validation, reporting will be very very custom and relations based on the data is out of the question. Is it very user or developer friendly? No. The xml blob could look something like this:

<customAttributeCollection> <customAttribute> <name>ICQAddress</name> <type>string</type> <displayName>ICQ Address</displayName> <description>The ICQ address of the contact.</description> <values> <value>0123456</value> </values> <length>50</length> </customAttribute> </customAttributeCollection>

Using ntext you've got quite an amount of space to store the xml blobs. In the schema I've outlined above, information is included to do simple custom validation (length / type), and it can potentially also support picklists. It might be wise to store structures such as this that map existing MS CRM attribute metadata.  This way there is a remote chance that the data can be mapped when MS CRM gets better support for custom fields. It would also be wise to name attributes containing this schema the same thing for all entities (like "customattributes") so these columns can be retreived and parsed easily. 

Maybe Microsoft should publish some kind of guidelines on how to customize beyond current limitations and think about supporting such customizations in the next major upgrade? For example they could publish a standard XSD for my Option 3 and make the upgrade software read and parse these fields.

One thing is for sure; our customers won't settle for a CRM system that allows less than an extra 10 nvarchar(50) fields on their contacts.

PS. If your've blown the 8k limit and decide on hacking the thing you might want to flip out completely and remove unwanted attributes, which is extremely unsupported.

UPDATE: I'm working on an actual XSD for the xml blobs, the draft is posted here. I use XSDObjectGen for generating the classfile.

5 Comments

  • The 8k limit on row size is a factor of SQL Server. 8k is the limit for a SQL Server row because data pages are 8k and rows cannot span pages. It is done this way for performance reasons.



    The point remains, and is well taken that given the size of the built-in attributes of CRM entities, little room exists for customization...



  • Well, at least they could have dedicated a customTable for each entity to, at the least, provide those 8k's.

  • The SQL Server 8k limit is a poor excuse for bad application design for entity extension. It is not a good idea anyhow to allow e.g. the contact entity's ContactBase table to be extended with custom fields, because now MS cannot add a single new field to the next MSCRM version, without risking that a client's database cannot be upgraded due to no more room in the tables.



    There are several other CRM systems out there, that allow virtually an unlimited number of extra fields, both on SQL Server and other RDBMSes. They do this by using separate extension table(s) for each entity. These other CRM systems that support this are e.g. Siebel and SalesMaker/growBusiness Solutions. Hey, even SuperOffice might support this better than MSCRM.

  • How integrated can approach 3 be into the CRM forms and workflow. Can I add extra tabs and access some or all of the XML elements and attributes on them? Can I add some of the elements and attributes to existing forms if I want them placed on existing forms? Lastly, what about the workflow: Will I be able to use any of the elements and attributes to trigger workflows?

  • Well, you definately have to get really creative if you want to use these xmlblobs for anything else than storage. Of course you can add the memo fields to forms, but they will display the raw xml.

Comments have been disabled for this content.