My requirements are:

 - Need to be able to dynamically add User-Defined fields of any data type
 - Need to be able to query UDFs quickly
 - Need to be able to do calculations on UDFs based on datatype 
 - Need to be able to sort UDFs based on datatype

Other Information:

 - I&#39;m looking for performance primarily
 - There are a few million Master records which can have UDF data attached
 - When I last checked, there were over 50mil UDF records in our current database
 - Most of the time, a UDF is only attached to a few thousand of the Master records, not all of them
 - UDFs are not joined or used as keys. They&#39;re just data used for queries or reports

Options:

 1. Create a big table with StringValue1, StringValue2... IntValue1, IntValue2,... etc. I hate this idea, but will consider it if someone can tell me it is better than other ideas and why.

 2. Create a dynamic table which adds a new column on demand as needed. I also don&#39;t like this idea since I feel performance would be slow unless you indexed every column.

 3. Create a single table containing UDFName, UDFDataType, and Value. When a new UDF gets added, generate a View which pulls just that data and parses it into whatever type is specified. Items which don&#39;t meet the parsing criteria return NULL.

 4. Create multiple UDF tables, one per data type. So we&#39;d have tables for UDFStrings, UDFDates, etc. Probably would do the same as #2 and auto-generate a View anytime a new field gets added

 5. XML DataTypes? I haven&#39;t worked with these before but have seen them mentioned. Not sure if they&#39;d give me the results I want, especially with performance.

 6. Something else? 

All User Defined-Fields Solutions on Gang of Coders

Total of 1 User Defined-Fields Solutions

How to design a database for User Defined Fields?