title: Most efficient method for persisting complex types with variable schemas in SQL
body: <p><strong>What I'm doing</strong></p>
  
  <p>I am creating an SQL table that will provide the back-end storage mechanism for complex-typed objects.  I am trying to determine how to accomplish this with the best performance.  I need to be able to query on each individual simple type value of the complex type (e.g. the String value of a City in an Address complex type).  </p>
  
  <p>I was originally thinking that I could store the complex type values in one record as an XML, but now I am concerned about the search performance of this design.  <strong>I need to be able to create variable schemas on the fly without changing anything about the database access layer</strong>.</p>
  
  <hr>
  
  <p><strong>Where I'm at now</strong></p>
  
  <p>Right now I am thinking to create the following tables.</p>
  
  <pre><code> TABLE:  Schemas
     COLUMN NAME       DATA TYPE
     SchemaId          uniqueidentifier
     Xsd               xml                 //contains the schema for the document of the given complex type
     DeserializeType   varchar(200)        //The Full Type name of the C# class to which the document deserializes.
  
   TABLE:  Documents
     COLUMN NAME       DATA TYPE      
     DocumentId        uniqueidentifier
     SchemaId          uniqueidentifier
  
   TABLE:  Values                            //The DocumentId+ValueXPath function as a PK
     COLUMN NAME       DATA TYPE      
     DocumentId        uniqueidentifier
     ValueXPath        varchar(250)
     Value             text
  </code></pre>
  
  <p>from these tables, when performing queries I would do a series of self-joins on the value table.  When I want to get the entire object by the DocumentId, I would have a generic script for creating a view mimics a denormalized datatable of the complex-type.</p>
  
  <hr>
  
  <p><strong>What I want to know</strong></p>
  
  <p>I believe there are better ways to accomplish what I am trying to, but I am a little too ignorant about the relative performance benefits of different SQL techniques.  Specifically I don't know the performance cost of:</p>
  
  <pre><code>1 - comparing the value of a text field versus of a varchar field.
  2 - different kind of joins versus nested queries
  3 - getting a view versus an xml document from the sql db
  4 - doing some other things that I don't even know I don't know would be affecting my query but, I am experienced enough to know exist
  </code></pre>
  
  <p>I would appreciate any information or resources about these performance issues in sql as well as a recommendation for how to approach this general issue in a more efficient way.   </p>
  
  <hr>
  
  <p><strong>For Example,</strong></p>
  
  <p>Here's an example of what I am currently planning on doing.</p>
  
  <p>I have a C# class Address which looks like</p>
  
  <pre><code>public class Address{
       string Line1 {get;set;}
       string Line2 {get;set;}
       string City {get;set;}
       string State {get;set;}
       string Zip {get;set;
  }
  </code></pre>
  
  <p>An instance is constructed from <code>new Address{Line1="17 Mulberry Street", Line2="Apt C", City="New York", State="NY", Zip="10001"}</code></p>
  
  <p>its XML value would be look like.</p>
  
  <pre><code>&lt;Address&gt;
     &lt;Line1&gt;17 Mulberry Street&lt;/Line1&gt;
     &lt;Line2&gt;Apt C&lt;/Line2&gt;
     &lt;City&gt;New York&lt;/City&gt;
     &lt;State&gt;NY&lt;/State&gt;
     &lt;Zip&gt;10001&lt;/Zip&gt;
  &lt;/Address&gt;
  </code></pre>
  
  <p>Using the db-schema from above I would have a single record in the Schemas table with an XSD definition of the address xml schema.  This instance would have a uniqueidentifier (PK of the Documents table) which is assigned to the SchemaId of the Address record in the Schemas table.  There would then be five records in the Values table to represent this Address.</p>
  
  <p>They would look like:</p>
  
  <pre><code>DocumentId                              ValueXPath        Value
  82415E8A-8D95-4bb3-9E5C-AA4365850C70    /Address/Line1    17 Mulberry Street
  82415E8A-8D95-4bb3-9E5C-AA4365850C70    /Address/Line2    Apt C
  82415E8A-8D95-4bb3-9E5C-AA4365850C70    /Address/City     New York
  82415E8A-8D95-4bb3-9E5C-AA4365850C70    /Address/State    NY
  82415E8A-8D95-4bb3-9E5C-AA4365850C70    /Address/Zip      10001
  </code></pre>
  
  <h1>Just Added a Bounty...</h1>
  
  <p>My objective is to obtain the resources I need in order to give my application a data access layer that is fully searchable and has a data-schema generated from the application layer that does not require direct database configuration (i.e. creating a new SQL table) in order to add a new aggregate root to the domain model.</p>
  
  <p>I am open to the possibility of using .NET compatible technologies other than SQL, but I will require that any such suggestions be adequately substantiated in order to be considered.</p>