Embedding and indexing custom fields in SQL DB

Question

I need to save a large table (several million or rows) that contains a large number of user fields (unknown at compile time, but probably 20 to 40 user fields). For me it is very important (in terms of performance) to be able to request data based on these custom fields: for example, "Select the rows in which this attribute has this value, this attribute is the value, etc." Each request contains from 20 to 30 WHERE clauses.

My ideas so far:

• Change the database schema each time a new custom field is implemented. Store each custom field as a column in a table. Add and maintain indexes in each user-created column. How to properly construct these indexes is a big problem, since I don’t know which attributes (columns) will be used in WHERE queries.

• Save custom fields as a column of type XML. As I understand from SQL2005, I can query inside XML in columns of type XML. Not so sure about performance though.

• Entity attribute value . This is what I use now, but it is a pain.

Any suggestions?

Edit: Some clarification on my requirements. I have a table, 40-50 million rows (say) of identification numbers and various attributes associated with these identifiers.

, 20 "CustomAttribute1", 2, 5 "CustomAttribute2", "", 3 "CustomAttribute20", ""

I need a FAST method of returning all IDs where:
     1. CustomAttribute1  = 2
     2. CustomAttribute2  = 'Yes'
     3. CustomAttribute4  = null
     4. CustomAttribute20  != 'No'  
  etc...

EAV: - , , DB , EAV , , .

Answer 1

, . EAV ( , , ), "" RDBMS-.

- -... , , . .

XML - , SQL Server . RDBMS, , .

(, 20+) , , , XML , . , EAV.

Answer 2

XML,

" , , , , UDF , ".

   <UDF>
      <Field Name="ConferenceAddress" DBType="NVarChar" Size="255">Some Address</Field>
      <Field Name="ConferenceCity" DBType="NVarChar" Size="255">Some City</Field>
      ...etc
   </UDF>

, , xml . .. , .

XML, XML, XML-, . ///Get

GetUDFFieldValue AddUDFField UpdateUDFField DeleteUDFField

- TableName ColumnName (, Dynamic SQL XML X X Column Name, / UDF)

XML Sql Server 2005 ( ):

http://technet.microsoft.com/en-us/library/ms345118 (v = sql.90).aspx

:

, ? NoSql , NoSql Sql Server.