title: Joining 100 tables
body: <p>Assume that I have a main table which has 100 columns referencing (as foreign keys) to some 100 tables (containing primary keys).</p>
  
  <p>The whole pack of information requires joining those 100 tables. And it is definitely a performance issue to join such a number of tables. Hopefully, we can expect that any user would like to request a bunch of data containing values from not more than some 5-7 tables (out of those 100) in queries that put conditions (in WHERE part of the query) on the fields from some 3-4 tables (out of those 100). Different queries have different combinations of the tables used to produce "SELECT" part of query and to put conditions in "WHERE". But, again, every SELECT would require some 5-7 tables and every WHERE would requre some 3-4 tables (definitely, the list of tables used to produce SELECT may overlap with the list of tables used to put conditions in WHERE). </p>
  
  <p>I can write a VIEW with the underlying code joining all those 100 tables. Then I can write the mentioned above SQL-queries to this VIEW. But in this case it is a big issue for me how to instruct SQL Server that (despite the explicit instructions in the code to join all those 100 tables) only some 11 tables should be joined (11 tables are enough to be joined to produce SELECT outcome and take into account WHERE conditions).</p>
  
  <p>Another approach may be to create a "feature" that converts the following "fake" code</p>
  
  <pre><code>SELECT field1, field2, field3 FROM TheFakeTable WHERE field1=12 and field4=5
  </code></pre>
  
  <p>into the following "real" code:</p>
  
  <pre><code>SELECT T1.field1, T2.field2, T3.field3 FROM TheRealMainTable 
  join T1 on ....
  join T2 on ....
  join T3 on ....
  join T4 on ....
  WHERE T1.field1=12 and T4.field4=5
  </code></pre>
  
  <p>From grammatical point of view, it is not a problem even to allow any mixed combinations of this "TheFakeTable-mechanism" with real tables and constructions. The real problem here is how to realize this "feature" technically. I can create a function which takes the "fake" code as an input and produces the "real" code. But it is not convenient because it requires using dynamic SQL tools evrywhere where this "TheFakeTable-mechanism" appears. A fantasy-land solution is to extend the gramma of the SQL-language in my Management Studio to allow writing such a fake code and then automatically converting this code into the real one before sending to the server.</p>
  
  <p>My questions are:</p>
  
  <ol>
  <li>whether SQl Server can be instructed shomehow (or to be genius enouh) to join only 11 tables instead of 100 in the VIEW described above?</li>
  <li>If I decide to create this "TheFakeTable-mechanism" feature, what would be the best form for the technical realization of this feature?</li>
  </ol>
  
  <p>Thanks to everyone for every comment!</p>
  
  <p>PS
  The structure with 100 tables arises from the following question that I asked here:
  <a href="https://stackoverflow.com/questions/14758912/normalizing-an-extremely-big-table">Normalizing an extremely big table</a></p>