I have been in similar situations.

Sometimes, for instance, the application is set to support more than one business unit or entity and those units may share a number of data and features, but they may also have a number of fields that are unique to them. If the number of these business units is large, it can be difficult to maintain custom fields through a DB structure for each of them.

In one particular scenario I was in, we provided a number of fields in our DB that we kept in the core data table, which we used meta data to describe based on the business unit. This required less joins and complexity on the DB, but involved more complexity in the application logic.

The XML suggestion seems like a good one if it is available (SQL 2000 folks out of luck I guess), of course if we are talking about lots of fields, then that could get some serious bloat.

It would be nice if sql server allowed for indexes using specific xpath queries, or better yet, if you could store the info using a format like JSON instead. Of course, for enterprising companies, it might be worth considering the creation of some CLR functions for storing and parsing data in that, or similar format.