Wednesday, November 23, 2005

MSCRM 3.0 added fields - row size limitation

MSCRM 1.2 had an undocumented limit to the number of (actually, the combined size of) fields you could add to an entity. At least the MBS marketing department did not know anything but "you can add any number of custom fields as you like". This limit is imposed by the SQL Server 2000 maximum row size of 8KB, minus some overhead for replication. In addition, v1.2 used updatable SQL views with 'before triggers', which further limited the available size. Some of the entities in v1.2 is quite large to begin with, e.g. the Contact entity, and you would soon hit the roof.

In v3.0, they have raised the limit by providing an extra full row for custom fields, i.e. a separate table on each entity for the added fields. In addition, SQL replication is gone, and so are the updatable views. Thus you will be able to exploit the full range of bytes in a row as you please. The new tables are named *ExtensionBase, e.g. AccountExtensionBase. All text is of course still unicode, thus each char will take up two bytes in the database row.

Note that all new custom fields are added to the *ExtensionBase table, custom fields are no longer injected into the native table of an entity.

SharePoint has a similar mechanism for custom metadata on lists; the metadata fields all share the same database table. Although this limitation exists, it rarely imposes practical restrictions in our solutions, and I think that the same will apply to MSCRM custom fields.

No comments: