title: Need input on DB design approach: indexing words per text file, remembering position
body: <p>I've got the following design problem:</p>
  
  <p>Say I have 1 million of plain text files approximately 10KB in size. My goal is to devise a way to store an index of all words, such that I can link each word to a particular text file and word's location within said file.</p>
  
  <p>Example:</p>
  
  <pre><code>Text file X contents: "The quick brown fox jumps over the lazy dog"
                         0   1     2     3   4     5    6   7    8
  
  Text file Y contents: "Now is the time for all good men"
                         0   1  2   3    4   5   6    7
  </code></pre>
  
  <p>I want to store roughly the following:</p>
  
  <pre><code>the   =&gt; {X,0}, {X,6}, {Y,2}
  quick =&gt; {X,1}
  is    =&gt; {Y,1}
  .... and so on
  </code></pre>
  
  <p>Obviously I'm not indexing plain-text file in reality, my indexer is a multithreaded C# application that distills input to terms "file", "word", "position". I can't create a typical lookup table set because number of rows will easily grow beyond 2 billion. </p>
  
  <p>My idea at first was to store pairs {message,position} in a text blob primary-keyed with word itself. With this solution, however, I am afraid that there will be a huge contention when all my threads will try to update a row for "the" with new pairs of {message,position}.</p>
  
  <p>I am locked to my environment, SQL Server Express 2012, so let's work with what we have.
  I can do anything with the database itself, in fact my app creates the database as part of normal workflow, hence I can deploy CLR stored procs if needed.</p>
  
  <p>Ideas?</p>