Cannot add more picklist or bit fields

I came across this issue today in a 4.0 environment that still uses SQL 2005. Time to upgrade, huh? Happily that's soon to be the case - so this hopefully is just for the record books.

Anyway the symptom was that while the user was able to add nvarchar fields to the accounts entity, it would error out each time the user wanted to add a new bit field (or for that matter a picklist) it would fail with an error message.

Using the trace tool it took pretty quick to identify the underlying cause. The following exception appeared in the trace file:

Exception: System.Data.SqlClient.SqlException: Too many table names in the query. The maximum allowable is 256.

So what is causing the "too many table names" in query?

Simple. Every time an attribute is added to an entity, the entity views (regular entity view and filtered view) are updated. The filtered view in particular joins to the StringMap view for picklists and bit fields to obtain the corresponding friendly "name" field. For example, for an account field called "new_flag" it will join to StringMap view to create a new virtual field in the FilteredAccount called "new_flagname".

One only needed to look up a little higher in the trace file to see the view being constructed with many joins for the bit and picklist fields. Such that if the number of these two types of field combined exceeds 256 (or thereabouts given other joins that may already exist) it will cause this join limitation to occur. Which is only a limitation on SQL 2005. This is no longer a limitation from SQL 2008 and up.

The options for resolving this issue are therefore:

1. Upgrade. Really. The technology you are using is around 7 years old (at least) and there does come a point where the compelling reason to upgrade is just the combined benefits of all the various enhancements that have been introduced over time (that is, if you cannot find a single compelling reason).
2. Clean up your system and review whether you actually do need all those fields in your environment. This actually is relevant whether you upgrade or not. I'm a big proponent of keeping the environment as clean as possible as my first post on this blog will attest to (disclosure: the above environment is extended directly by the client as we like to encourage our clients to do so they are not reliant on us for every little change required).